Hi all,
I have a following code which unions the dataset for each month of the year. I want to write a macro for it so that the manual intervention of copying the same query for each month can be avoided. Please help.
select distinct PName,PAdd,PCity
from Direc.Prod_P1_2014_12 where PNumber is not null
union
select distinct PName,PAdd,PCity
from Direc.Prod_P1_2014_11 where PNumber is not null
union
select distinct PName,PAdd,PCity
from Direc.Prod_P1_2014_10 where PNumber is not null
.
.
.
union
select distinct PName,PAdd,PCity
from Direc.Prod_P1_2014_01 where PNumber is not null
%macro product;
%local year month dsname ;
%do year=2014 %to 2015;
%do month=1 %to 12;
%let dsname=DIREC.PROD_P1_&year._%sysfunc(putn(&month,Z2));
%if %sysfunc(exist(&dsname)) %then &dsname ;
%end;
%end;
%mend;
data want ;
set %product ;
...
I have written the following macro code for the above issue, which is working fine for me. But can I optimise it further ? As the month values in the dataset name are mentioned as 01, 02...09, but SAS is resolving the values as 1,2,3...9. Is there any solution available for this issue, so that I can provide only a single DO loop for the month ?
%macro product;
%do i=2014 %to 2015;
%do i=1 %to 9;
%if %sysfunc(exist(Direc.Prod_P1_&i._0&j.)) %then %do;
Direc.Prod_P1_&i._&j.;
%end;
%end;
%do i=10 %to i=12;
%if %sysfunc(exist(Direc.Prod_P1_&i._&j.)) %then %do;
Direc.Prod_P1_&i._&j.;
%end;
%end;
%end;
%mend;
%macro product;
%local year month dsname ;
%do year=2014 %to 2015;
%do month=1 %to 12;
%let dsname=DIREC.PROD_P1_&year._%sysfunc(putn(&month,Z2));
%if %sysfunc(exist(&dsname)) %then &dsname ;
%end;
%end;
%mend;
data want ;
set %product ;
...
It worked like a charm Tom, thank you very much
what the following line will do in your program?
%if %sysfunc(exist(&dsname)) %then &dsname ;
The main purpose of a SAS macro is to conditionally generate SAS code.
This macro in particular will generate a list of dataset names.
So that line tests if the dataset exists and if it does it produces the dataset name as part of the SAS code that the macro is generating. If the dataset doesn't exist then no SAS code is generated.
Why not write it in SAS instead of SQL? Then no need for a macro since you can use a dataset list.
data want ;
set direc.prod_P1_2014_: ;
where PNUMBER is not null;
keep pname padd pcity ;
run;
proc sort nodupkey ;
by pname padd pcity;
run;
Thanks for your reply Tom.
Sorry, I missed one part in my query, actually I need to repeat this code for each month from 2013 onwards till now, I have written one macro for the same & had posted it here but it is pending for approval with the admin, once it will be approved kindly share your valuable insights on the same.
Though if some modification can be done in your code addressing my issue, kindly share that as well.
If your library not cluttered with similar table names then dataset lists could solve the problem.
set direc.prod_P1_201: ;
Or
set direc.prod_P1_2013_: direc.prod_P1_2014_: direc.prod_P1_2015_: ;
What is the selection logic for which dataset to include?
Can the selection logic be imbedded into the data step instead by selecting on a variable in the dataset? Or by using INDSNAME= option to generate a variable with the data set name.
You can also pull dataset names from metadata such as DICTIONARY.TABLES.
proc sql noprint ;
select catx(',',libname,memname) into :dslist separated by ' '
from dictionary.tables
where libname='DIREC'
and memname like 'PROD^_P1^_201%^_%' escape '^'
order by libname,memname
;
...set &dslist ;
You can do time loops using INTNX and INTCK functions.
%macro product(start,end);
%local i n month dsname ;
%do i=0 %to %sysfunc(intck(month,&start,&end));
%let month=%sysfunc(intnx(month,&start,&i),yymmdds10);
%let dsname=DIREC.PROD_P1_%sysfunc(tranwrd(%substr(&month,1,7),/,_));
%if %sysfunc(exist(&dsname)) %then &dsname ;
%end;
%mend;
%put %product('01NOV2013'd,'01MAR2014'd);
Hi Tom,
I'm curious how the macro you created will work if you add a LIBNAME to the code? Thanks
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.