topic can I use loops in Macro? in SAS Programming

can I use loops in Macro?

rpg163 — Mon, 28 Nov 2011 13:31:08 GMT

Hi,

I am trying to use loops in macro, like this

%macro Read(num=);

%let n=0;

%do i=1 %to &num;

%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 &num;

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

can I use loops in Macro?

art297 — Mon, 28 Nov 2011 13:52:45 GMT

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.

Re: can I use loops in Macro?

rpg163 — Mon, 28 Nov 2011 14:13:47 GMT

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;

Re: can I use loops in Macro?

art297 — Mon, 28 Nov 2011 14:32:22 GMT

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

Re: can I use loops in Macro?

rpg163 — Mon, 28 Nov 2011 14:35:39 GMT

You are right, I just modify the code, It works now.

THX!