DATA Step, Macro, Functions and more

How to read multiple dataset with Macro Variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

How to read multiple dataset with Macro Variable

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. 

 


Accepted Solutions
Solution
‎10-16-2016 07:59 AM
Respected Advisor
Posts: 4,173

Re: How to read multiple dataset with Macro Variable

[ Edited ]

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


All Replies
Super User
Super User
Posts: 7,074

Re: How to read multiple dataset with Macro Variable

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.

Frequent Contributor
Posts: 92

Re: How to read multiple dataset with Macro Variable

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. 

Super User
Posts: 19,855

Re: How to read multiple dataset with Macro Variable

 

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. 

 

Super User
Posts: 5,516

Re: How to read multiple dataset with Macro Variable

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

Frequent Contributor
Posts: 92

Re: How to read multiple dataset with Macro Variable

Posted in reply to Astounding

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

Solution
‎10-16-2016 07:59 AM
Respected Advisor
Posts: 4,173

Re: How to read multiple dataset with Macro Variable

[ Edited ]

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

Frequent Contributor
Posts: 92

Re: How to read multiple dataset with Macro Variable

Hi Patrick, 

 

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

 

Super User
Posts: 5,516

Re: How to read multiple dataset with Macro Variable

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.

 

Frequent Contributor
Posts: 92

Re: How to read multiple dataset with Macro Variable

Posted in reply to Astounding

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 764 views
  • 2 likes
  • 5 in conversation