Bringing in multiple datasets from one library into another using a macro

[skhan9]

Hi all,

I am connecting to my Oracle SQL schema using SAS Enterprise Guide. Once I have my SQL datasets into SAS (total of 8 datasets), I want to call all the datasets, individually, into the Work library. I am writing the code below to bring in one dataset into Work library at a time:

DATA WORK.DATASET_1;
SET MYSQL.DATASET_1; 
RUN;

DATA WORK.DATASET_2;
SET MYSQL.DATASET_2; 
RUN;

.

.

.

DATA WORK.DATASET_8;
SET MYSQL.DATASET_8; 
RUN;

I have tried the following macro:

%MACRO ST (DS);
PROC SORT DATA = MYSQL.&DS OUT=WORK.&DS;
BY PRIMARY_ID;
RUN;
%MEND;
%ST(DATASET_1);
%ST(DATASET_2);
%ST(DATASET_3);
%ST(DATASET_4);
%ST(DATASET_5);
%ST(DATASET_6);
%ST(DATASET_7);
%ST(DATASET_8);

This takes a very long to run as the datasets are large and they are being sorted. Is there a way to bring in the datasets into Work library (individual datasets) using a macro without using proc sort?

Thank you for the help!

[SASJedi · Accepted solution]

You can copy them in bulk using PROC COPY. Try something like this:

PROC COPY IN=MYSQL OUT=WORK;
   /* List the name of each table you want to copy to WORK */ 
   SELECT DATASET1 DATASET2 DATASET2 DATASET 4;
RUN;

Alternatively, you could just access the tables directly in Oracle in your SAS program without copying them out first.

May the SAS be with you!

Mark

[PaigeMiller]

Maybe you could do the sorting when you issue the SQL call to Oracle, I would imagine it might be faster in Oracle than in SAS data sets.

Then combine them all via this code

data want;
    set mysql.dataset:;
run;

Note the colon after DATASET

[Reeza]

proc datasets lib=mysql; *input library;
copy out=work;*output library;
select dataset_1-dataset_8;
run;quit;

Use PROC DATASETS or PROC COPY instead. No need for macros or data steps. 

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p19ugnx9u6hi6xn1t1gsiyg6l3ag.htm

---

@skhan9 wrote:

Hi all,

I am connecting to my Oracle SQL schema using SAS Enterprise Guide. Once I have my SQL datasets into SAS (total of 8 datasets), I want to call all the datasets, individually, into the Work library. I am writing the code below to bring in one dataset into Work library at a time:

DATA WORK.DATASET_1;
SET MYSQL.DATASET_1; 
RUN;

DATA WORK.DATASET_2;
SET MYSQL.DATASET_2; 
RUN;

.

.

.

DATA WORK.DATASET_8;
SET MYSQL.DATASET_8; 
RUN;

I have tried the following macro:

%MACRO ST (DS);
PROC SORT DATA = MYSQL.&DS OUT=WORK.&DS;
BY PRIMARY_ID;
RUN;
%MEND;
%ST(DATASET_1);
%ST(DATASET_2);
%ST(DATASET_3);
%ST(DATASET_4);
%ST(DATASET_5);
%ST(DATASET_6);
%ST(DATASET_7);
%ST(DATASET_8);

This takes a very long to run as the datasets are large and they are being sorted. Is there a way to bring in the datasets into Work library (individual datasets) using a macro without using proc sort?

Thank you for the help!

---

[skhan9]

Thank you all for the solutions! Both, proc copy and proc dataset, work amazingly well and fast!