topic Re: Macro to create multiple similar files based on different periods in one go in SAS Programming

Macro to create multiple similar files based on different periods in one go

Question — Wed, 10 May 2017 11:59:13 GMT

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');*/

Re: Macro to create multiple similar files based on different periods in one go

PaigeMiller — Wed, 10 May 2017 12:22:45 GMT

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.

Re: Macro to create multiple similar files based on different periods in one go

RW9 — Wed, 10 May 2017 12:24:38 GMT

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

Re: Macro to create multiple similar files based on different periods in one go

Question — Wed, 10 May 2017 12:25:28 GMT

Thank you PaigeMiller for your response.

Sorry I didn't express myself properly! This code works fine but copying 25
time as you said , it's not efficient!
I was after the do loop..

Thank you

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Macro to create multiple similar files based on different periods in one go

Kurt_Bremser — Wed, 10 May 2017 12:33:47 GMT

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.