SAS: Create SAS column name from a string field

SAS: Create SAS column name from a string field

%macro var_name_from_string(input=);

%* make a SAS variable name from a string ;

%* 1. keep numeric, alpha, underscore, and blanks ;

%* 2. replace multiple blanks with a single blank ;

%* 3. remove trailing blanks ;

%* 4. convert remaining blanks to underscore ;

%* 5. limit to 20 characters so I can prepend prefix such as dmy_acct_;

substr(translate(left(compbl(compress(strip(&input), "", "NSK"))), "_", " "),1,20)

%mend var_name_from_string;

data work.test;

attrib s length=$50;

s = %var_name_from_string(input="ABCDE"); output;

s = %var_name_from_string(input="AB CDE"); output;

s = %var_name_from_string(input="AB(C)DE  "); output;

s = %var_name_from_string(input="AB C_D!E"); output;

run;

proc sql noprint;

create table work.catgys as

select distinct KEY_CATGY

, %var_name_from_string(input=key_catgy) as CATGY_DMY

from &MY_TABLE

;

quit;

run;

data _null_;

set work.catgys end=eof;

call symput("catgy" || trim(left(_n_)), KEY_CATGY);

call symput("catgydmy" || trim(left(_n_)), CATGY_DMY);

if (eof) then do;

      call symput("catgys", trim(left(_n_)));

end;

run;

%macro create_dummy_fields;

data &MY_TABLE_DMY;

set &MY_TABLE;

%do i = 1 %to &CATGYS;

      dmy_catgy_&&CATGYDMY&i = (KEY_CATGY = "&&CATGY&i");

%end;

run;

%mend create_dummy_fields;

%create_dummy_fields;