Iterative Macro Variable code

[Aljay]

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?

[Murray_Court]

I think you are missing an &,

l

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

[RW9]

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;

[RW9]

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;

[Reeza]

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;