DATA Step, Macro, Functions and more

How do I write a dynamic do loop macro?

Reply
Contributor
Posts: 21

How do I write a dynamic do loop macro?

Hello SAS Experts,

 

This is my first do loop and this particular task is way over my head.  Thank you very much in advance for your time and effort.  I would greatly appreciate your help on this!  This is on PC SAS 9.4.

 

I'm trying to execute a do loop macro that will dynamically use proc sql to run a simple query and create three new tables.  The proc sql query would take the form below and should compare the last full month of production (DB.TABLE_11801) and perform an inner join with a production table from two months prior (DB.TABLE_11711) and create a new table with the results of that join. 

 

Ideally, the macro would be dynamic and execute this query three times with a two month gap between each table in the query.

 

So, if this was run now in Feb 2018, the first execution would be (TABLE_11801 joined with TABLE_11711, then on iteration (2) TABLE_11712 joined on TABLE_11710, then on the final 3rd iteration TABLE_11711 joined on TABLE_11709).  Three new tables would be created with the results from these three executions of the proc sql below.

 

CREATE TABLE X_&MACDATEVAR AS
(
SELECT
T1.ACCT_KEY, T2.ACCT_KEY, T1.REPORTED_MONTH, T2.REPORTED_MONTH, T1.EVDESC, T2.EVDESC, COUNT(*)FROM DB1.TABLE_11801 AS T1 INNER JOIN(SELECT * FROM DB2.TABLE_11711) AS T2 ON T1.ACCT_KEY = T2.ACCT_KEY AND T1.EVDESC = T2.EVDESC GROUP BY 1,2,3,4,5,6;
)

That part is straight forward for me but writing the do loop section of the macro and getting that to correctly put the macro date suffix at the end of each of the table names queried is far above my ability.  I found the do loop code below (credit to Proc Star ChrisNZ) that I think would work for this but I'm still having trouble figuring out how it would all piece together.

 

---- This is the do loop piece that I thought would work here (credit to Proc Star ChrisNZ) ---------------------------------------------------------

 

%macro query(date);
%local monthno sasdate outdata date;
%do monthno = 0 %to 2;
%let sasdate = %sysfunc(intnx(month,%sysfunc(inputn(&date,yymmdd10.)),&monthno));
%let outdata = TABLE_%sysfunc(putn(&sasdate,yymmdd4.)); 
%put &=outdata;
%let outdata = TABLE_%sysfunc(putn(&sasdate,yymmdd4.)); 
%let dateq = %unquote(%nrbquote('%sysfunc(putn(&sasdate,yymmddd10.))'));
data &outdata.;
X=&dateq.;
run;
%end;
%mend;
%query(2017-10-01);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

If anyone can figure out how the do loop would need to be constructed to do this, I would be very grateful!  Thank you again for your help.

 

~ Eric

Super User
Posts: 23,937

Re: How do I write a dynamic do loop macro?

1. Create a macro that joins your data with the data sets you need identified as the parameters

2. Use a data _null_ step to create the months/dates and then use CALL EXECUTE to call the macro from #1.

Contributor
Posts: 21

Re: How do I write a dynamic do loop macro?

Hi Reeza, 

Sorry, but this is still above my understanding.  Can you provide an example?

 

Data step_null_;
/*Date = intnx('month', today(), 0, 'b');*/
%let DATE = %SYSFUNC(intnx(MONTH,"&SYSDATE"D,-1), YYMMN4.);
Date_Last_Month = %sysfunc(intnx('month', &date, -1, 'b'),yymmn4.);
Date_Month2 = intnx('month', date, -2, 'b');
%put &DATE;
run;

 

 

Thanks,

Eric

 

 

Super User
Posts: 23,937

Re: How do I write a dynamic do loop macro?

Macro:

 

%macro join_tables(in_dsn1 = , in_dsn2=, out_dsn=);

CREATE TABLE X_&out_dsn AS

SELECT 
    T1.ACCT_KEY, 
    T2.ACCT_KEY, 
    T1.REPORTED_MONTH,
    T2.REPORTED_MONTH,
    T1.EVDESC, 
    T2.EVDESC,
    COUNT(*)FROM DB1.&in.dsn2 AS T1

INNER JOIN DB2.&in_dsn2 AS T2

ON T1.ACCT_KEY = T2.ACCT_KEY
AND T1.EVDESC = T2.EVDESC
GROUP BY 1,2,3,4,5,6;

%mend join_tables;

%join_tables(in_dsn1 =Table_11801 , in_dsn2= Table_11711, out_dsn=out_name);

 

 

The next step is building a table that has the dates you need to run, I'll let you attempt that first. Then JOIN_TABLES call can be called from CALL EXECUTE. 

 

If you can't get it working, post back with what you've tried and what isn't working.

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 148 views
  • 0 likes
  • 2 in conversation