topic Re: Loop in SQL into: in SAS Programming

Loop in SQL into:

hhchenfx — Mon, 29 Mar 2021 02:10:34 GMT

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;

Re: Loop in SQL into:

Tom — Mon, 29 Mar 2021 02:20:50 GMT

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;

Re: Loop in SQL into:

hhchenfx — Mon, 29 Mar 2021 03:53:57 GMT

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

Re: Loop in SQL into:

mkeintz — Mon, 29 Mar 2021 04:02:04 GMT

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.

Re: Loop in SQL into:

ChrisNZ — Mon, 29 Mar 2021 05:29:13 GMT

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;

Re: Loop in SQL into:

Kurt_Bremser — Mon, 29 Mar 2021 08:29:16 GMT

Use CALL EXECUTE straight from the dataset.

Re: Loop in SQL into:

Tom — Mon, 29 Mar 2021 11:51:22 GMT

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;