BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
skhan9
Fluorite | Level 6

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

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

 

 

 

Check out my Jedi SAS Tricks for SAS Users

View solution in original post

4 REPLIES 4
SASJedi
SAS Super FREQ

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

 

 

 

Check out my Jedi SAS Tricks for SAS Users
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Reeza
Super User
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
Fluorite | Level 6

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2044 views
  • 2 likes
  • 4 in conversation