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

Dear All, 

I have written a proc statement in SAS as below:

proc sql;
create table var.ACXMSR_MKEPUBSUB as
select a.INDIV_ID, a.MSR_INDIV_ID, b.ROLE_CD, b.SUB_ORDER_NBR, b.AGENCY_CD, b.SUB_ORDER_DT, b.PAID_AMT, b.PROD_ID, b.CANCEL_DT, b.SOURCE_RECEIVED_CD, b.PAYMENT_DT,
b.PAYMENT_STATUS_CD, b.PAYMENT_TYPE_MKE_CD, b.ORDER_DOCUMENT_KEY_CD, b.CANCEL_TYPE_CD
from var.FF_ACX_MSR_ID10 a, ora1.FACT_MKE_PUB_SUBSCRIPTION b
where a.MSR_INDIV_ID = b.INDIVIDUAL_ID;
run;

 

In the where clause i have mentioned 2 data tables however like this i have 100 data tables as we are migrating from Oracle to SAS hence it is very tedious to keep writing this code again and again, also the data is huge 200+ GB hence i thought of using a macro. 

Before the code proc sql, i am thinking of using % let statement like

% let newvar = name of dataset ;

%let newvar1 = name of other dataset; 

i plan to call these in the proc sql statement in the where clause, Please advice if this approach is correct or i should go for some other approach. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

Below some sample code how you can execute the same code using different table names.

%macro demo(tbl_name);
  title "Table is: &tbl_name";
  proc print data=&tbl_name;
  run;
%mend;

data _null_;
  input table :$41.;
  call execute('%demo('||table||')');
  datalines;
sashelp.class
sashelp.company
;
run;

Given the volumes you're dealing with also consider if you can eventually do the counts first separately within SAS and Oracle and only then combine the results over your grouping variables. This would reduce data volumes exchanged between SAS and Oracle and though improve performance.

 

From what you've told us I also believe that you would have to use a full outer join instead of an implicit inner join (your Where clause). 

View solution in original post

9 REPLIES 9
Tom
Super User Tom
Super User

There is a limit to the number of tables that a single SQL statement can use.

Why are you using SQL instead of a DATA step?  You can merge hundreds of datasets with a single data step if you want.

Shivi82
Quartz | Level 8

HI, 

 

I do not have to merge the data sets. 

 

As said one table is in Oracle and other in SAS hence with the proc sql statement i am picking one variable from Oracle data table  and similar variable from SAS table say individual id and fnding the difference in count for each variable. 

 

Thanks. 

Reeza
Super User

 

Here's a quick intro to macro page that can help you get started

http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/

 

Depending on how big your data table A is, you may want to consider a macro variable with IDs listed instead. Then the entire query can run on the server. When SAS has to use data on a server and some other location ALL the data is first brought into SAS and then filtered/analyzed. 

 

Astounding
PROC Star

Are the variable names always the same ... just the table names that are changing?

Shivi82
Quartz | Level 8

Yes correct, the variable names are always the same. it is just that the table names change. 

Patrick
Opal | Level 21

Below some sample code how you can execute the same code using different table names.

%macro demo(tbl_name);
  title "Table is: &tbl_name";
  proc print data=&tbl_name;
  run;
%mend;

data _null_;
  input table :$41.;
  call execute('%demo('||table||')');
  datalines;
sashelp.class
sashelp.company
;
run;

Given the volumes you're dealing with also consider if you can eventually do the counts first separately within SAS and Oracle and only then combine the results over your grouping variables. This would reduce data volumes exchanged between SAS and Oracle and though improve performance.

 

From what you've told us I also believe that you would have to use a full outer join instead of an implicit inner join (your Where clause). 

Shivi82
Quartz | Level 8

Hi Patrick, 

 

Thank you for the explanation and code. This is very helpful. 

 

Astounding
PROC Star

While CALL EXECUTE is a valuable tool, it's probably 2 or 3 steps beyond where you are right now  Here's a more basic approach.  First, define a macro:

 

%macro one_run (dataset1=, dataset2=);

proc sql;
create table var.ACXMSR_MKEPUBSUB as
select a.INDIV_ID, a.MSR_INDIV_ID, b.ROLE_CD, b.SUB_ORDER_NBR, b.AGENCY_CD, b.SUB_ORDER_DT, b.PAID_AMT, b.PROD_ID, b.CANCEL_DT, b.SOURCE_RECEIVED_CD, b.PAYMENT_DT,
b.PAYMENT_STATUS_CD, b.PAYMENT_TYPE_MKE_CD, b.ORDER_DOCUMENT_KEY_CD, b.CANCEL_TYPE_CD
from &dataset1 a, &dataset2 b
where a.MSR_INDIV_ID = b.INDIVIDUAL_ID;
run;

%mend one_run;

 

Then run the macro as many times as necessary.  One line of 100:

 

%one_run (dataset1=var.FF_ACX_MSR_ID10, dataset2=ora1.FACT_MKE_PUB_SUBSCRIPTION)

 

Note that the text substitution does not take place in the WHERE clause.  Rather, it takes place just before that when defining the aliases "a" and "b".

 

To automate this process with CALL EXECUTE,  you would need (1) the macro definition, plus (b) a SAS data set holding the pairs of data set names.  CALL EXECUTE could automate calling the macro 100 times.  So if you already have the list of data set names as a DATA data set, CALL EXECUTE helps.  But if you don't already have a SAS data set with the pairs of data set names, it's just as easy to call the macro 100 times.

 

Shivi82
Quartz | Level 8

This is so simple and this is what i was looking for. I dont recall any other forum taking so much pain to explain the code and process in detail. 

 

Thank you all for extending support. This is one reason as to why i believe no other software can come closer to SAS as this is one awesome community. 

 

Cheers... 

 

 

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
  • 9 replies
  • 4767 views
  • 2 likes
  • 5 in conversation