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
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
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
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
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
can someone use union to do this? and comment on all these methods in the large number of datasets environment?
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;
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
Hi PG,
Congratulations!!!
Thanks! I am held to higher standards now! - PG
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.