can I use loops in Macro?

[rpg163]

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

[art297]

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.

[rpg163]

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;

[art297]

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

[rpg163]

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

THX!