BookmarkSubscribeRSS Feed
Question
Fluorite | Level 6

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Question
Fluorite | Level 6
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. -##
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 718 views
  • 1 like
  • 4 in conversation