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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.