SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Rhodochrosite | Level 12

Hi Everyone,

Can you please help me with the below issue?
For each row, I want to create a macro variable taking value of  "var" column as below.
I can do it row by row till the end, however is there anyway to create a macro do something like
%DO i=1 %to endoffile?

Thank you,

HHC

 

 

data have;
input id var;
datalines;
1 111
2 222
3 333
;
run;

%macro mymacro(c=);
	data _temp; set have;
	if _N_=&c;
	run;

	proc sql;
	select var into: var_&c from _temp;
	quit;
%mend;
%mymacro(c=1);
%mymacro(c=2);
%mymacro(c=3);

%put &var_1; *should returnn value 111;
%put &var_2; *should returnn value 222;
%put &var_3; *should returnn value 333;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

Like this?

data _null_;
  set HAVE;
  call execute(catx(' ', 'proc sql; create table MYDATA'|| put(_N_,9.0 -l), 'as'
                       , 'select a.*,', LIST_OF_SOME_ELEMENTS
                       , 'from MYDATA join', FILENAME
                       , 'on a.xyz=b.xyz; quit;'
              ));
run;

 

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

There is no need to use any macro code to do that.  You can just do it in a data step:

data _null_;
  set have;
  call symputx(cats('var_',_n_),var);
run;

Or even in an SQL step:

proc sql noprint;
select var into :var_1 - from have;
quit;

But WHY are you taking data out of your dataset and putting it into macro variables? 

Why not just do what ever operations you are doing on all of the observations in HAVE at the same time?

And if do need to use the value of VAR to run some complex macro then just call the macro right from the data step and skip defining all of those macro varaibles.

data _null_;
  set have;
  call execute(cats('%nrstr(%mymacro)(',var,')'));
run;
hhchenfx
Rhodochrosite | Level 12

Hi Tom,

My real work is that I have 2 columns:

file_name | List_of_Some_element

 

For each rows, I want to create 2 variables:  &filename , and &list_of_some_element

Then I will do the SQL like

create table Mydata as as select a.*, &list_of_some_element from Mydata join &filename
on a.xyz=b.xyz; quit;

That's why I want to store the column value into macro variable.

So after looping through all rows, the final MyData will have all elements I need to use.

Thank you,

HHC

 

 

 

 

ChrisNZ
Tourmaline | Level 20

Like this?

data _null_;
  set HAVE;
  call execute(catx(' ', 'proc sql; create table MYDATA'|| put(_N_,9.0 -l), 'as'
                       , 'select a.*,', LIST_OF_SOME_ELEMENTS
                       , 'from MYDATA join', FILENAME
                       , 'on a.xyz=b.xyz; quit;'
              ));
run;

 

Tom
Super User Tom
Super User

Sounds like the goal is to use the list of variables to merge bunch of datasets.  So assuming that the datasets with the "variables" have a maximum of one observation per value of XYZ then should be able to just use one data step to combine all of them.

data want;
  merge mydata 
        dataset1(keep=xyz vara )
        dataset2(keep=xyz varb varc)
        dataset3(keep=xyz vard)
  ;
  by xyz;
run;

So just use the data in your metadata table to generate that code.  So if your metadata set looks like:

data varlist ;
   input dataset :$41. varname :$32. ;
cards;
dataset1 vara
dataset2 varb
dataset2 varc
dataset3 vard
;

Then this data step will generate the code and the %INCLUDE will run it.

filename code temp;
data _null_;
  set varlist end=eof;
  by dataset ;
  file code;
  if _n_=1 then put 'data want;' / @3 'merge mydata ';
  if first.dataset then put @9 dataset '(keep=xyz ' @;
  put varname @;
  if last.dataset then put ')';
  if eof then put @3 ';' / @3 'by xyz;' / 'run;' ;
run;
%include code / source2;
mkeintz
PROC Star

If I understand correctly what you want, then you can

 

proc sql noprint;
  select var into :var_1- from have order by id;
quit;

Then you can loop VAR_1 through VAR_&sqlobs for your subsequent work.

 

Just be sure to use an upper limit (200 in my example) that is at least as large as the number of observations in HAVE.   Just remembered you don't need a specific upper limit.

True, if you have ID's 1,2,3 and 6, but not 4 and 5, then macrovar VAR_4 will have value 666.  But that does not appear to be a problem for the task you describe.

 

 

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1002 views
  • 11 likes
  • 5 in conversation