topic Re: Appending multiple datasets using macro in SAS Programming

Appending multiple datasets using macro

KP12 — Wed, 04 Mar 2015 07:39:13 GMT

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

Re: Appending multiple datasets using macro

KP12 — Thu, 05 Mar 2015 01:10:22 GMT

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;

%do i=10 %to i=12;

%if %sysfunc(exist(Direc.Prod_P1_&i._&j.)) %then %do;

Direc.Prod_P1_&i._&j.;

%end;

%mend;

Re: Appending multiple datasets using macro

Tom — Thu, 05 Mar 2015 01:17:02 GMT

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;

Re: Appending multiple datasets using macro

KP12 — Thu, 05 Mar 2015 01:28:40 GMT

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.

Re: Appending multiple datasets using macro

Tom — Thu, 05 Mar 2015 02:02:25 GMT

If your library not cluttered with similar table names then dataset lists could solve the problem.

set direc.prod_P1_201: ;

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 ;

Re: Appending multiple datasets using macro

Tom — Thu, 05 Mar 2015 02:05:32 GMT

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

%mend;

data want ;

set %product ;

...

Re: Appending multiple datasets using macro

KP12 — Thu, 05 Mar 2015 02:16:42 GMT

It worked like a charm Tom, thank you very much

Re: Appending multiple datasets using macro

Tom — Thu, 05 Mar 2015 02:18:46 GMT

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

Re: Appending multiple datasets using macro

RamKumar — Thu, 05 Mar 2015 09:14:13 GMT

what the following line will do in your program?

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

Re: Appending multiple datasets using macro

Tom — Thu, 05 Mar 2015 13:02:59 GMT

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.

Re: Appending multiple datasets using macro

Yawkusi — Tue, 17 Nov 2015 12:58:30 GMT

Hi Tom,

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