%let mvarlist=A B C D;
The SAS data set temp has two variables Var1 and Var2 with four observations. The goal is to assgin A, B, C, D in mvarlist to a new sas variable Var3 in temp1 data so that Var3 has A, B, C, and D for its 4 observations repectively.
data temp1;
set temp;
Var3=%scan(&mvarlist, _N_, ' ');
run;
(1) I know the above argument _N_ in %scan() is not right since it need a number, not a sas variable. How to achieve the desired result?
(2) proc sql has a way to output a sas variable column to a macro var list, is there a way to do the opposite: write a macro list to a sas variable?
(3) What if mvarlist=1 2 3 4, I want to input it as a numeric SAS variable?
Thanks.
%let mvarlist=A B C D;
data temp1;
set temp;
Var3=scan("&mvarlist", _N_, ' ');
run;
You are so close, but you need to remember that code generated by macro processing is compiled BEFORE the data step starts running.
So instead of the macro %SCAN() function which would run just once you need to use the data step SCAN() function that will execute for each iteration of the data step. (Note also that your %SCAN() function cannot work since you have passed a character string, _N_, as the second argument where an integer is needed.)
VAR3 = scan( "&mvarlist",_n_) ;
Or to make a numeric variable if the values of MVARLIST were digits instead of letters you can add an INPUT() function.
%let value_list=1 2 3 4 ;
...
VAR3 = input(scan("&value_list",_n_),32.) ;
%LET mvarlist= A B C D;
data have;
input var1;
cards;
1
2
3
4
;
data temp1;
do until(scan("&mvarlist", _n_, ' ') ne ' ');
set have;
Var3=scan("&mvarlist", _n_, ' ');
end;
run;
1. Why use do until loop, is it really necesary?
2. The set statement can be put outside of do loop?
No, you don't need a do loop. Did you try stat's or Tom's suggested code? The following worked for me:
%LET mvarlist= A B C D;
data have;
input var1 var2;
cards;
1 4
2 3
3 2
4 1
;
data want;
set have;
Var3=scan("&mvarlist", _n_);
run;
As for proc sql, sql has no way to know record order, so no method could be guaranteed to work unless you have a variable that indicates record order.
That said, the following will work, but may not always produce the same results:
proc sql;
create table want as
select *, scan("&mvarlist.",monotonic()) as var3
from have
;
quit;
1 and 2 has been solved by Jagadishkatam.
3) Just use Var3=input(scan("&mvarlist", _n_, ' '),3.);
Why use macro variables for this in the first place? Base SAS/SQL both have perfectly good merging procedures. Put you macro variables in a dataset and use normal merging techniques. Far simpler, easier to read/use code.
Using SAS data merging is an alterntive way. The reason is that I am going to write a macro so that input parameter is easier to create as a macro list than creating a sas data as a input parameter.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.