DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 50

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

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

Trusted Advisor
Posts: 1,228

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

%let mvarlist=A B C D;

data temp1;

set temp;

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

run;

Super User
Super User
Posts: 7,039

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

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


Trusted Advisor
Posts: 1,137

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

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

Thanks,
Jag
Contributor
Posts: 50

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

Posted in reply to Jagadishkatam

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

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

PROC Star
Posts: 7,468

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

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;

Super User
Posts: 7,764

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

1 and 2 has been solved by Jagadishkatam.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

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

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.

Contributor
Posts: 50

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

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.

Ask a Question
Discussion stats
  • 8 replies
  • 828 views
  • 0 likes
  • 7 in conversation