topic Re: Iterative Macro Variable code in SAS Programming

Iterative Macro Variable code

Aljay — Wed, 19 Mar 2014 15:43:29 GMT

libname NACO 'sas/data/reference';

%macro NACO (ext=);

Proc Sql;

Create Table naco as select

Select count(*) from naco.NAco_ext.;

quit;

%Mend NACO;

%NACO (ext=201302);

I want to be able to replace the ext =201302 with multiple months from 201302 to 201402 and put results into one table.. Basically, end result is one table with record counts from a year's worth of monthly tables.

Can anyone help?

Re: Iterative Macro Variable code

Murray_Court — Wed, 19 Mar 2014 16:19:49 GMT

I think you are missing an &,

ibname NACO 'sas/data/reference';

%macro NACO (ext=);

Proc Sql;

Create Table naco as select

Select count(*) from naco.NAco_&ext.;

quit;

%Mend NACO;

%NACO (ext=201302);

Re: Iterative Macro Variable code

RW9 — Wed, 19 Mar 2014 16:19:58 GMT

Here's one way - create your empty table. Then using call execute generate each insert - note the yyymm is just a number so you could format it, and I have just used sashelp.cars, so you could re-work that for your data specifically):

proc sql;
create table NACO
(
MNTH num,
CNT num
);
quit;

data _null_;
do i=201302 to 201402;
    call execute('proc sql;
                              insert into NACO
                              set MNTH='||strip(put(i,best.))||',
                                  CNT=(select count(1) from sashelp.cars);     /* Note change this to - from NACO_'||strip(put(I,best.)||') - for your datasets */
                            quit;');
    end;
run;

Re: Iterative Macro Variable code

RW9 — Wed, 19 Mar 2014 16:27:42 GMT

Just to add, you can also use call execute to call your macro if you need to:

libname NACO 'sas/data/reference';

%macro NACO (ext=);

Proc Sql;

Create Table naco as select

Select count(*) from naco.NAco_&ext.;

quit;

%Mend NACO;

data _null_;
do i=201302 to 201402;
call execute('%NACO (ext='||strip(put(I,best.))||');');
end;
run;

Re: Iterative Macro Variable code

Reeza — Wed, 19 Mar 2014 16:52:29 GMT

You should be querying the Dictionary table for this instead.

Your macro doesn't make sense as well, because it will always replace the table NACO so you'll only have the last iteration.

You can customize the where clause to get info on only the datasets your interested in.

proc sql;

create table want as

select libname, memname, nobs, nvar, nlobs

from dictionary.tables

where libname='NACO' and memname like "NACO_%";

quit;