Convert string with spaces to valid table name

Question

I want to create a series of tables using SAS macro language, but the strings I am trying to pass through have spaces in them. Any ideas on what to add to make them valid table names?

%macro has_spaces(string);

proc sql;
create table &string. as 
select

*

from my_table
;
quit;
%mend;

%has_spaces(has 2 spaces);

Thanks.

Note, do you want to just get the has-2-spaces value into &string, or do you want to make it a legal table name? — Joe, Jul 23 '14 at 14:30

score 3 · Accepted Answer · answered Jul 23 '14 at 14:29

3

Another option is translate:

%macro has_spaces(string);
 proc sql;
  create table %sysfunc(translate(&string.,_,%str( ))) as 
    select *
    from my_table
  ;
 quit;
%mend;

answered Jul 23 '14 at 14:29

Joe

62,789
6
49
67

Am I able to embed this code in the macro call? i.e.: %has_spaces(%sysfunc(translate(string,_,%str( ))); – CaptainBear Jul 23 '14 at 18:00
It's possible to do so, but you shouldn't. You should do that sort of processing in the macro itself (input cleansing/validation). – Joe Jul 23 '14 at 18:31

Robert Penridge · Answer 2 · 2020-03-10T16:24:19.197

You could do something like this as this will catch pretty much anything that isnt valid for a SAS table name and replace it with an underscore. We use a similar approach when creating file names based on customer names that contain all kinds of weird symbols and spaces etc... :

Macro Version:

%macro clean_tablename(iField=);
  %local clean_variable;

  %let clean_variable = %sysfunc(compress(&iField,,kns));
  %let clean_variable = %sysfunc(compbl(&clean_variable));
  %let clean_variable = %sysfunc(translate(&clean_variable,'_',' '));

  &clean_variable

%mend;

Test Case 1:

%let x = "kjJDHF f'ke''''j d (kdj-328) *#& J#ld!!!";
%put %clean_variable(iField=&x);

Result:

kjJDHF_fkej_d_kdj328_Jld

Your test case:

%macro has_spaces(string);

  proc sql;
    create table %clean_variable(iField=&string) as 
    select *
    from sashelp.class
    ;
  quit;

%mend;

%has_spaces(has 2 spaces);

Result:

NOTE: Table WORK.HAS_2_SPACES created, with 19 rows and 5 columns.

FCMP Version:

proc fcmp outlib=work.funcs.funcs;

  function to_valid_sas_name(iField $) $32;  
    length clean_variable $32;
    clean_variable = compress(iField,'-','kns');
    clean_variable = compbl(clean_variable);
    clean_variable = translate(cats(clean_variable),'_',' ');
    clean_variable = lowcase(clean_variable);
    return (clean_variable);
  endsub;

run;

Example FCMP Usage:

data x;
 length invalid_name valid_name $100;
 invalid_name = "kjJDHF f'ke''''j d (kdj-328) *#& J#ld!!!";
 valid_name   = to_valid_sas_name(invalid_name);
 put _all_;
run;

Result:

invalid_name=kjJDHF f'ke''''j d (kdj-328) *#& J#ld!!! valid_name=kjjdhf_fkej_d_kdj-328_jld

Please note that there are limits to what you can name a table in SAS. Ie. it must start with an underscore or character, and must be no more than 32 chars long. You can add additional logic to do that if needed...

Note, there are functions for checking if strings are valid to be used as (libname/datasetname/etc.); see `mvalid()` for dataset names ('member'), for example. — Joe, Jul 23 '14 at 18:32

score 1 · Answer 3 · answered Jul 23 '14 at 14:31

Compress out the spaces - one method is to use the datastep compress() function within a %SYSFUNC, e.g.

%macro has_spaces(string);
  proc sql;
    create table %SYSFUNC(compress(&string)) as 
    select
    *
    from my_table
    ;
  quit;
%mend;

%has_spaces(has 2 spaces);

score 0 · Answer 4 · answered Jul 31 '14 at 05:26

0

Just put the table name in quotes followed by an 'n' eg if your table name is "Table one" then pass this as the argument "Table one"n.

answered Jul 31 '14 at 05:26

Aparajith Chandran

139
10

Convert string with spaces to valid table name

4 Answers4

Macro Version:

FCMP Version: