04-08-2015 10:24 PM
%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.
Var3=%scan(&mvarlist, _N_, ' ');
(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?
04-08-2015 10:57 PM
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.) ;
04-09-2015 01:09 AM
%LET mvarlist= A B C D;
do until(scan("&mvarlist", _n_, ' ') ne ' ');
Var3=scan("&mvarlist", _n_, ' ');
04-10-2015 05:24 PM
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;
input var1 var2;
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:
create table want as
select *, scan("&mvarlist.",monotonic()) as var3
04-09-2015 03:02 AM
04-09-2015 04:17 AM
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.
04-10-2015 03:55 PM
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.