%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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.