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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ankitsas
Calcite | Level 5

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

10 REPLIES 10
damanaulakh88
Obsidian | Level 7

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

Jagadishkatam
Amethyst | Level 16

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
Ankitsas
Calcite | Level 5

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

ZRick
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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;

PGStats
Opal | Level 21

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
Linlin
Lapis Lazuli | Level 10

Hi PG,

Congratulations!!!Smiley HappySmiley HappySmiley Happy

PGStats
Opal | Level 21

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

PG
ZRick
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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

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
  • 1955 views
  • 0 likes
  • 7 in conversation