DATA Step, Macro, Functions and more

Appending multiple datasets using macro

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Appending multiple datasets using macro

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


Accepted Solutions
Solution
‎03-04-2015 09:05 PM
Super User
Super User
Posts: 6,502

Re: Appending multiple datasets using macro

%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


All Replies
Contributor
Posts: 24

Re: Appending multiple datasets using macro

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;

Solution
‎03-04-2015 09:05 PM
Super User
Super User
Posts: 6,502

Re: Appending multiple datasets using macro

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

...

Contributor
Posts: 24

Re: Appending multiple datasets using macro

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

Regular Contributor
Posts: 168

Re: Appending multiple datasets using macro

what the following line will do in your program?

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

Super User
Super User
Posts: 6,502

Re: Appending multiple datasets using macro

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.

Super User
Super User
Posts: 6,502

Re: Appending multiple datasets using macro

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;

Contributor
Posts: 24

Re: Appending multiple datasets using macro

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.

Super User
Super User
Posts: 6,502

Re: Appending multiple datasets using macro

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 ;

Super User
Super User
Posts: 6,502

Re: Appending multiple datasets using macro

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

Frequent Learner
Posts: 1

Re: Appending multiple datasets using macro

Hi Tom,

 

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 2932 views
  • 1 like
  • 4 in conversation