topic Re: How to Pass a macro var list to a SAS data variable? in SAS Programming

How to Pass a macro var list to a SAS data variable?

Macro — Thu, 09 Apr 2015 02:24:21 GMT

%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.

Re: How to Pass a macro var list to a SAS data variable?

stat_sas — Thu, 09 Apr 2015 02:41:26 GMT

%let mvarlist=A B C D;

data temp1;

set temp;

Var3=scan("&mvarlist", _N_, ' ');

run;

Re: How to Pass a macro var list to a SAS data variable?

Tom — Thu, 09 Apr 2015 02:57:12 GMT

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.) ;

Re: How to Pass a macro var list to a SAS data variable?

Jagadishkatam — Thu, 09 Apr 2015 05:09:31 GMT

%LET mvarlist= A B C D;

data have;

input var1;

cards;

;

data temp1;

do until(scan("&mvarlist", _n_, ' ') ne ' ');

set have;

Var3=scan("&mvarlist", _n_, ' ');

end;

run;

Re: How to Pass a macro var list to a SAS data variable?

Kurt_Bremser — Thu, 09 Apr 2015 07:02:13 GMT

1 and 2 has been solved by Jagadishkatam.

3) Just use Var3=input(scan("&mvarlist", _n_, ' '),3.);

Re: How to Pass a macro var list to a SAS data variable?

RW9 — Thu, 09 Apr 2015 08:17:17 GMT

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.

Re: How to Pass a macro var list to a SAS data variable?

Macro — Fri, 10 Apr 2015 19:55:04 GMT

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.

Re: How to Pass a macro var list to a SAS data variable?

Macro — Fri, 10 Apr 2015 20:49:23 GMT

1. Why use do until loop, is it really necesary?

2. The set statement can be put outside of do loop?

Re: How to Pass a macro var list to a SAS data variable?

art297 — Fri, 10 Apr 2015 21:24:05 GMT

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;