BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KP12
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

...

View solution in original post

10 REPLIES 10
KP12
Fluorite | Level 6

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;

Tom
Super User Tom
Super User

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

...

KP12
Fluorite | Level 6

It worked like a charm Tom, thank you very much Smiley Happy

RamKumar
Fluorite | Level 6

what the following line will do in your program?

  %if %sysfunc(exist(&dsname)) %then &dsname ;

Tom
Super User Tom
Super User

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.

Tom
Super User Tom
Super User

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;

KP12
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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 ;

Tom
Super User Tom
Super User

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

Yawkusi
Calcite | Level 5

Hi Tom,

 

I'm curious how the macro you created will work if you add a LIBNAME to the code? Thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 10268 views
  • 2 likes
  • 4 in conversation