topic macro to merge datasets with same dataset name but different libnames in SAS Programming

macro to merge datasets with same dataset name but different libnames

ZRick — Tue, 12 Mar 2013 07:18:07 GMT

I want to merge rs1.t, rs2.t, rs3.t into mpp with 3 rows, but my dd macro doesn't work, how do I fix it.

%macro t;

%do i =1 %to 3 ;

libname rs&i "/parallel/&i";

data rs&i..t;

x=&i;

run;

%end;

%mend;

%t;

%macro dd;

%do i =1 %to 3 ;

libname rs&i "/parallel/&i";

data mpp;

merge rs&i..t;

%end;

run;

%mend;

%dd

Re: macro to merge datasets with same dataset name but different libnames

damanaulakh88 — Tue, 12 Mar 2013 07:55:15 GMT

This will work try:-

%macro t;

%do i =1 %to 3 ;

libname rs&i "/shared/BatchServer";

data rs&i..t&i.;

x=&i;

run;

%end;

%mend;

%t;

%macro dd;

proc sql;

drop table mpp;

quit

%do i =1 %to 3 ;

libname rs&i "/shared/BatchServer";

;

proc append base=mpp data= rs&i..t&i.;

run;

%end;

%mend;

%dd;

/Daman

Re: macro to merge datasets with same dataset name but different libnames

Jagadishkatam — Tue, 12 Mar 2013 10:02:16 GMT

Hi,

Please follow the below code, and check how it resolves, for this i have used options mprint and symbolgen this purpose. the log will provide you a better idea. Also as per your earlier code, you can create several libraries within the same folder, so "/parallel" is enough, i dont think "/parallel/&i" works, it will not create the libraries. could you please check this once again and let me know

%macro t;

%do i =1 %to 3 ;

libname rs&i "/parallel";

data rs&i..t;

x=&i;

run;

%end;

%mend;

%t;

options mprint symbolgen;

The below macro will set the different datasets in different libraries with the same name, i hope this is the code you are looking for

%macro set;

data mpp;

set

%do i = 1 %to 3;

rs&i..t

%end;

;

by x;

run;

%mend;

%set;

Thanks,

Jagadish

Re: macro to merge datasets with same dataset name but different libnames

Ankitsas — Tue, 12 Mar 2013 10:06:17 GMT

Below is the changed code -

x=&i;

run;

%end;

%mend;

%t;

%macro dd;

data mpp;

set

%do i =1 %to 3 ;

rs&i..t

%end;

;

run;

%mend;

%dd

Note - i removed libname and replaced merge with set statement.

Regards,

Ankit

Re: macro to merge datasets with same dataset name but different libnames

ZRick — Tue, 12 Mar 2013 16:51:11 GMT

can someone use union to do this? and comment on all these methods in the large number of datasets environment?

Re: macro to merge datasets with same dataset name but different libnames

Tom — Tue, 12 Mar 2013 17:15:29 GMT

There are a couple of issues that are different if SQL UNION is used.

1) SAS had limit of about 16 (has this changed?) tables referenced in one SQL statement.

2) If the structures of the tables are not identical then SQL is less forgiving. That could be a good thing or a bad thing depending on your project. You can use UNION CORRESPONDING to handle when the order of the variables within the table are different.

For example here is how to convert the DD macro in the post above to generate SQL instead of data step.

%macro dd;

proc sql ;

create table mpp as

%do i =1 %to 3 ;

%if &i > 1 %then union ;

select * from rs&i..t

%end;

;

quit;

%mend;

Re: macro to merge datasets with same dataset name but different libnames

PGStats — Tue, 12 Mar 2013 18:12:19 GMT

Hi Tom,

In SAS 9.3, up to 32 tables and views can be joined in a query. I don't know if the same limit applies to set operations. Adding ALL to the union operation can be much faster if you don't care about duplicate rows.

Re: macro to merge datasets with same dataset name but different libnames

Linlin — Tue, 12 Mar 2013 18:31:18 GMT

Hi PG,

Congratulations!!!

Re: macro to merge datasets with same dataset name but different libnames

ZRick — Tue, 12 Mar 2013 20:33:02 GMT

I am trying to mimic this loop and delete the data sets t in each folder after the merging, it gives me the error, how do I fix it?

%macro dd;

data mpp;

set %do i =1 %to 3 ;

rs&i..t

%end;

;

run;

proc datasets library= %do kk=1 %to 3; rs&i %end; nolist nodetails;

delete t;

quit;

%mend;

%dd

Re: macro to merge datasets with same dataset name but different libnames

Tom — Tue, 12 Mar 2013 21:38:05 GMT

It would probably be easier to use SQL to drop the tables.

%macro dd;

data mpp;

set

%do i =1 %to 3 ;

rs&i..t

%end;

;

run;

proc sql ;

%if not (&syserr) %then %do i=1 %to 3;

drop table rs&i..t ;

%end;

%else %put NOTE: Datasets NOT deleted because of error. ;

quit;

%mend;

%dd

Re: macro to merge datasets with same dataset name but different libnames

PGStats — Tue, 12 Mar 2013 22:18:25 GMT

Thanks! I am held to higher standards now! - PG