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