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');*/
Can you tell us what part of this code is slow? Is it the PROC SQL? Or something else?
As a side issue, instead of type %weekly_reports(...) 25 times, this could be called in a %do loop inside the macro and eliminate all that typing.
Its not surprising it is slow. You repeatedly go back to the database extracting information. Address your process as a whole;
1) Extract all the data you will need in one step.
2) Process the data as you need to.
I.e.
proc sql; create table HAVE as select * from DB; quit; data want; set have; /* Assign weekly periods to the data */ week=...; run; /* Create weekly report */ ods excel file=...; proc report data=want...; by week; title "Week is #byval1"; columns _all_; run; ods excel close;
There is no need to use macro, nor a need to split the same data into many different blocks - which is also another big resource cost (storage maybe not so much, but read/write on lots of datasets is).
Try to pull the whole table, starting from your earliest transaction date, into SAS in one sweep. While you do that, you can create your variables cust_group and most_trans. Also create a variable week based on the transaction_date, which you can use as an additional by value when you do the summarization. That will do away with the multiple passes through the ODBC connection.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.