topic Bringing in multiple datasets from one library into another using a macro in New SAS User

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

skhan9 — Fri, 08 Jul 2022 17:44:53 GMT

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!

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

SASJedi — Fri, 08 Jul 2022 17:51:29 GMT

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

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

PaigeMiller — Fri, 08 Jul 2022 17:52:48 GMT

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

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

Reeza — Fri, 08 Jul 2022 17:54:59 GMT

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!

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

skhan9 — Fri, 08 Jul 2022 19:22:16 GMT

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