CODEWITHSUNDEEP
sassas-macro
CaptainBear
CaptainBear

Reputation: 167

Convert string with spaces to valid table name

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.

Upvotes: 3

Views: 872

Answers (4)

Robert Penridge
Robert Penridge

Reputation: 8513

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...

Upvotes: 2

Aparajith Chandran
Aparajith Chandran

Reputation: 140

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.

Upvotes: 0

Chris J
Chris J

Reputation: 7769

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);

Upvotes: 1

Joe
Joe

Reputation: 63424

Another option is translate:

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

Upvotes: 3

Related Questions