Hi All,
I have created this macro, it creates identical tables (same variables etc) for different periods. This code works fine, but it can be time consumig if I wanted to produce it for lots of different periods. Could you please advise me a more elegant way?
Thank you very much
/**************************************************/ /*MACRO TU RUN WEEKLY REPORTS */ /****************************************************/
%macro weekly_reports (p, i, insert );
%put &i.; %let days=%sysevalf(%sysfunc(round(365.25/50*&i.,1))-1); %put &days.;
proc sql;
connect to odbc (user=&user. password=&password. dsn=CDM_IQ); create table work.Report_&p. as select * from connection to odbc(
SELECT
WEEK_END_DATE
,(CASE WHEN B.CATEGORY ='0' THEN 'CONTROL'
WHEN B.CATEGORY = '1' THEN 'REDUCED MERCH'
WHEN B.CATEGORY = '3' THEN 'TARGET'
WHEN B.CATEGORY IS NULL THEN 'OUTSIDE OF TEST'
ELSE 'CHECK' END) AS CUST_GROUP
,(CASE WHEN A.MOST_TRANS <= 1 THEN 'A. ONE OR LESS'
WHEN A.MOST_TRANS = 2 THEN 'B. TWO'
WHEN A.MOST_TRANS = 3 THEN 'C. THREE'
WHEN A.MOST_TRANS = 5 THEN 'D. FIVE'
WHEN A.MOST_TRANS = 10 THEN 'E. TEN'
ELSE 'F. OTHER' END) AS MOST_TRANS
,COUNT(DISTINCT(A.CUSTOMER_ID)) AS CUSTOMERS
,SUM(A.SALES) AS SALES
,SUM(A.PLAYS) AS TRANS
FROM WORK.TABLE_RAW
WHERE TRANSACTION_DATE BETWEEN &Insert. AND DATEADD(Day, &days., &Insert.)
GROUP BY
WEEK_END_DATE
,CUST_GROUP
,MOST_TRANS ;
);
disconnect from odbc;
quit;
%mend;
/*Week by week*/
%weekly_reports(w1,1,'2016-11-20'); %weekly_reports(w2,1,'2016-11-27'); %weekly_reports(w3,1,'2016-12-04'); %weekly_reports(w4,1,'2016-12-11'); %weekly_reports(w5,1,'2016-12-18');
%weekly_reports(w6,1,'2016-12-25'); %weekly_reports(w7,1,'2017-01-01'); %weekly_reports(w8,1,'2017-01-08'); %weekly_reports(w9,1,'2017-01-15'); %weekly_reports(w10,1,'2017-01-22');
%weekly_reports(w11,1,'2017-01-29'); %weekly_reports(w12,1,'2017-02-05'); %weekly_reports(w13,1,'2017-02-12'); %weekly_reports(w14,1,'2017-02-19'); %weekly_reports(w15,1,'2017-02-26');
%weekly_reports(w16,1,'2017-03-05'); %weekly_reports(w17,1,'2017-03-12'); %weekly_reports(w18,1,'2017-03-19'); %weekly_reports(w19,1,'2017-03-26'); %weekly_reports(w20,1,'2017-04-02');
%weekly_reports(w21,1,'2017-04-09'); %weekly_reports(w22,1,'2017-04-16'); %weekly_reports(w23,1,'2017-04-23'); %weekly_reports(w24,1,'2017-04-30');
/*%weekly_reports(w25,1,'2017-05-07');*/
... View more
