Thank you very much for adding the Macro function. I really appreciate your help. I did a slight adjusted to your code so that it can deal with what ever the variable name format was. just that you know the code that I used is below:
thank you again for your help
proc sql noprint;
/*count all columns in all the data sets in the given library*/
select left(put(count(*),8.))
into :numds
from dictionary.columns
where libname='WORK' and upcase(memname)=upcase('mysurvey');
/*store all columns names as macro variables cl1 - cl&numds*/
select name
into :cl1 - :cl&numds
from dictionary.columns
where libname='WORK' and upcase(memname)=upcase('mysurvey');
quit;
quit;
%put &numds;
%macro DO_BRANCH;
data want;
set mysurvey;
%do i = 1 %to &numds;
%let nana=&&cl&i;/*nana is the columns name*/
if &nana='' then &nana.numeric = .;
if anydigit(substr(&nana., 1, 1))= 0 and not(&nana='') then &nana.numeric = 999;/*if columns doesn't start with a number, write 999*/
else &nana.numeric = substr(&nana., 1, 1);/*if columns doesn't start with a number, write it*/
if &nana.numeric in (8,9) then &nana.numeric = .;/*if columns value is either 9 or 8 write "."*/
%end;
run;
%mend DO_BRANCH;
%DO_BRANCH;
... View more
