Hi,
I am struggling with the macro codes to dynamically extract characters of the values from specific columns and put into newly created columns.
My actual data has tens of columns to be converted. Here I've made a sample data with 3 representative columns:
data oriData;
input ID aab $7. bbc $7.;
cards;
1 0outof1 0outof0
2 0outof0 0outof1
3 0outof0 0outof0
4 0outof1 0outof1
5 0outof0 0outof1
;
run;
My goal is to convert the sample data into the one like the following data set:
data finalData;
input ID BC_aab LC_aab BC_bbc LC_bbc;
cards;
1 0 1 0 0
2 0 0 0 1
3 0 0 0 0
4 0 1 0 1
5 0 0 0 1
;
run;
Here are the codes I prepared to do this conversion:
/* Collect column information */
proc sql;
create table vartable as
select * from sashelp.vcolumn
where libname='WORK' and memname="ORIDATA";
quit;
/* Make a list of column names to be converted */
data varlist;
set vartable;
where name not in ("ID");
keep name;
run;
/* Pass the column names into macro variables */
proc sql noprint;
select name
into :vname1 -
from work.varlist;
%let numobs=&sqlobs;
quit;
/* Examine the macro variables generated */
%put numobs= &sqlobs;
%put vname1= &vname1.;
%put vname2= &vname2.;
%put vname3= &vname3.;
/* Macro for the conversion */
%macro trfm(input,output);
data &output;
set &input;
%do i= 1 %to &numobs.;
BC_&&vname&i= %sysfunc(inputc(%substr(&&vname&i,1,1),8.));
LC_&&vname&i= %sysfunc(inputc(%substr(&&vname&i,%length(&&vname&i),1),8.));
drop &&vname&i.;
%end;
run;
%mend;
/* Testing */
%trfm(oriData,finalData);
Here is the log after running the code:
1921 data oriData;
1922 input ID aab $7. bbc $7.;
1923 cards;
NOTE: The data set WORK.ORIDATA has 5 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
1929 ;
1930 run;
1931
1932 proc sql;
1933 create table vartable as
1934 select * from sashelp.vcolumn
1935 where libname='WORK' and memname="ORIDATA";
NOTE: Table WORK.VARTABLE created, with 3 rows and 18 columns.
1936 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
1937
1938 data varlist;
1939 set vartable;
1940 where name not in ("ID");
1941 keep name;
1942 run;
NOTE: There were 2 observations read from the data set WORK.VARTABLE.
WHERE name not = 'ID';
NOTE: The data set WORK.VARLIST has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
1943
1944 proc sql noprint;
1945 select name
1946 into :vname1 -
1947 from work.varlist;
1948 %let numobs=&sqlobs;
1949 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
1950
1951 %put numobs= &sqlobs;
numobs= 2
1952 %put vname1= &vname1.;
vname1= aab
1953 %put vname2= &vname2.;
vname2= bbc
1954 %put vname3= &vname3.;
vname3= ccd
1955
1956 %macro trfm(input,output);
1957 data &output;
1958 set &input;
1959 %do i= 1 %to &numobs.;
1960 BC_&&vname&i= %sysfunc(inputc(%substr(&&vname&i,1,1),8.));
1961 LC_&&vname&i= %sysfunc(inputc(%substr(&&vname&i,%length(&&vname&i),1),8.));
1962 drop &&vname&i.;
1963 %end;
1964 run;
1965 %mend;
1966
1967 %trfm(oriData,finalData);
MPRINT(TRFM): data finalData;
MPRINT(TRFM): set oriData;
MPRINT(TRFM): BC_aab= a ;
MPRINT(TRFM): LC_aab= b ;
MPRINT(TRFM): drop aab;
MPRINT(TRFM): BC_bbc= b ;
MPRINT(TRFM): LC_bbc= c ;
MPRINT(TRFM): drop bbc;
MPRINT(TRFM): run;
NOTE: Variable a is uninitialized.
NOTE: Variable b is uninitialized.
NOTE: Variable c is uninitialized.
NOTE: There were 5 observations read from the data set WORK.ORIDATA.
NOTE: The data set WORK.FINALDATA has 5 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
The problem is that the program can't do extraction from the value of macro variables. Instead, it did extraction from the text of the macro variables. I couldn't find the solution, and ask for help from the community. Thanks in advance!!!
