BookmarkSubscribeRSS Feed
Macro
Obsidian | Level 7

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

8 REPLIES 8
stat_sas
Ammonite | Level 13

%let mvarlist=A B C D;

data temp1;

set temp;

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

run;

Tom
Super User Tom
Super User

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


Jagadishkatam
Amethyst | Level 16

%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
Macro
Obsidian | Level 7

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

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

art297
Opal | Level 21

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Macro
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2807 views
  • 0 likes
  • 7 in conversation