DATA Step, Macro, Functions and more

macro to merge datasets with same dataset name but different libnames

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

macro to merge datasets with same dataset name but different libnames

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


Accepted Solutions
Solution
‎03-12-2013 06:06 AM
Occasional Contributor
Posts: 14

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

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

View solution in original post


All Replies
Frequent Contributor
Posts: 81

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

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

Trusted Advisor
Posts: 1,137

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

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

Thanks,
Jag
Solution
‎03-12-2013 06:06 AM
Occasional Contributor
Posts: 14

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

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

Frequent Contributor
Posts: 133

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

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

Super User
Super User
Posts: 7,039

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

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;

Respected Advisor
Posts: 4,920

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

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.

PG

PG
Super Contributor
Posts: 1,636

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

Hi PG,

Congratulations!!!Smiley HappySmiley HappySmiley Happy

Respected Advisor
Posts: 4,920

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

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

PG
Frequent Contributor
Posts: 133

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

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

Super User
Super User
Posts: 7,039

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

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 493 views
  • 0 likes
  • 7 in conversation