Monday, August 21, 2017

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;