I added a compress function to keep the valid sas characters and a substr to only extract the variable name and left your translate . Seems to be working now.
%macro colrename_compress(table); %local rename_list sqlobs; proc sql ; select catx('=',nliteral(name),compress(translate(substr(name,1,length(name)),"_"," "),,'kn')) into :rename_list separated by ' ' from sashelp.vcolumn where libname=%upcase("%scan(work.&table,-2,.)") and memname=%upcase("%scan(&table,-1,.)") and indexc(trim(name),' ') ; quit; %if &sqlobs %then %do ; proc datasets lib=%scan(WORK.&table,-2); modify %scan(&table,-1); rename &rename_list; run; quit; %end; %mend colrename_compress;
data faminc; input 'famid 1'n 'famid .2'n faminc1-faminc11; cards; 1 3281 3413 3114 2500 2700 3500 3114 -999 3514 1282 2434 2818 2 4042 3084 3108 3150 -999 3100 1531 2914 3819 4124 4274 4471 3 6015 6123 6113 -999 6100 6200 6186 6132 -999 4231 6039 6215 ; run;
options symbolgen mprint mlogic; %colrename_compress(faminc);
... View more