How to read multiple dataset with Macro Variable

[Shivi82]

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. 

[Tom]

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]

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]

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]

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

[Shivi82]

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

[Patrick · Accepted solution]

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]

Hi Patrick, 

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

[Astounding]

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]

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...