- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Use CALL EXECUTE straight from the dataset.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
--------------------------