DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 96

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

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Trusted Advisor
Posts: 1,933

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

[ Edited ]

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.

Frequent Contributor
Posts: 96

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

Posted in reply to PaigeMiller
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. -##
Super User
Super User
Posts: 7,997

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

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

Super User
Posts: 7,866

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 110 views
  • 1 like
  • 4 in conversation