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