Hi,
I am trying to use loops in macro, like this
%macro Read(num=);
%let n=0;
%do i=1 %to #
%let n=%eval(&n+1);
proc sql noprint;
select A.var
, catx('_','WIND',var)
into :varname
, :outds
from var_index as A
where A.Num=&n;
quit;
%dataread(varname=&varname, outds=&outds);
%end;
%mend Read;
%Read(num=17);
Well, I just get the first dataset, and I don't know why.
and if I do this, it seems work.
%macro Read(num=);
%let n=0;
%do i=1 %to #
%let n=%eval(&n+1);
proc sql noprint;
select A.var
, catx('_','WIND',var)
into :varname
, :outds
from var_index as A
where A.Num=&n;
quit;
data &outds
set var_index;
if var=&varname;
run;
%end;
%mend Read;
%Read(num=17);
can anyone tell me what's the problem?
Many thanks!!!
There could be more than one problem. First, though, a question: why do you create macro variable &n, when you already are creating a macro variable &i as a result of your loop?
Second, if the file(s) you are reading have more than one record, you probably have to add "separated by" clauses so that the resulting macro variables can have more than one value.
Third, the forum can't know if other problems exist in the macro you call at the end of the first macro because you didn't post that code.
Many thanks!
Well, first yes. I should &i. I will modify the code.
second, because there is just one variable, so I just skip the "separated by".
third the macro is as follows,
* Macro_Rread;
%include "E:\Program\Macro\Macro_Pre_Adv.sas";
%macro Read(varname=,outds=);
proc sql noprint;
select cats('"',catx('\',path,table),'"')
into :path
from var_index as A
where A.var="&varname";
quit;
%Pre_Adv(input=&path,output=&outds,var=&varname);
proc sql noprint;
select label into :label
from var_index as A
where A.var="&varname";
quit;
data &outds;
set &outds;
label &varname="&label";
run;
%mend Read;
%macro Pre_Adv(input=,output=,var=);
data IPO;
set stk.WIND_IPO;
run;
PROC IMPORT out=zxczxc1
datafile=&input
DBMS=EXCEL REPLACE;
GETNAMES=YES;
mixed=yes;
SCANTEXT=no;
RUN;
data zxc1(keep=stkcd stknme) zxc2(drop=_COL0 _COL1 stkcd stknme);
retain stkcd stknme;
set zxczxc1;
%let dsid=%sysfunc(open(zxczxc1));
%let n=%sysfunc(attrn(&dsid,nobs));
%let close=%sysfunc(close(&dsid));
stkcd=input(substr(_COL0,1,6),$12.);
stknme=input(_COL1,$12.);
if _N_=&n or _N_=&n-1 THEN DELETE;
run;
proc sql noprint;
select catx('=','asd'||name,name)
, catx('=','asd'||name,label)
, cats('asd'||name,'=input(',name,',best12.)')
, name
into :rename separated by ' '
, :label separated by ' '
, :assign separated by ';'
, :drop separated by ' '
from dictionary.columns
where libname='WORK'
and memname='ZXC2';
quit;
data zxc3;
retain &drop;
set zxc2;
&assign;
rename &rename;
label &label;
drop &drop;
run;
data zxc4;
merge zxc1 zxc3;
run;
proc sort data=zxc4;
by stkcd;
run;
proc transpose data=zxc4 out=zxc5;
by stkcd;
run;
data zxc6;
set zxc5;
varid=anydigit(_LABEL_,1);
year=input(substr(_LABEL_,varid,4),best12.);
&var=COL1;
run;
data zxc7;
merge zxc6 IPO;
by stkcd;
if IPO_date='' then IPO_date=mdy(01,01,1990);
if year<year(IPO_date) then delete;
run;
data &output(keep=stkcd year &var);
set zxc7;
run;
proc datasets library=work;
delete zxczxc1 zxc1 zxc2 zxc3 zxc4 zxc5 zxc6 zxc7 IPO;
run;quit;
%mend Pre_Adv;
In your original code you are calling a macro called dataread, but I didn't see any such macro in your code. Additionally, (1) you end the call with a semicolon .. which you shouldn't and (2) the code you are including contains a macro with the same name as your macro (i.e., read).
You are right, I just modify the code, It works now.
THX!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.