topic Re: SAVE %MACRO OUTPUT in SAS Programming

SAVE %MACRO OUTPUT

viviwu — Mon, 24 Jul 2017 13:06:33 GMT

Hello,

I have a question about the macro, my codes are as following:

%let week_nb = 2;

%MACRO week(date_deb=,date_week=);

PROC SQL;
CREATE TABLE Client_base AS 

SELECT
B1.sName as Boutique
,R0.pop as Type
,COUNT(DISTINCT R0.ID) as nb_client
,SUM(S1.fTotalPrice) as Turnover

FROM save.liste_clients R0

INNER JOIN VALIBLA2.VA_NmsRecipient R1 ON (R0.ID = R1.iRecipientId)
INNER JOIN VALIBLA2.VA_ClientSegmentChina R2 ON (R0.ID = R2.iRecipientId)

LEFT JOIN VALIBLA2.va_sale S1 ON (R1.iRecipientId = S1.iSaleRecipientId AND &date_deb. <= datepart(S1.tsDate) <= &date_week. 
 AND S1.iSaleBoutiqueId IN (1345,1355)
 AND S1.fTotalPrice > 0)
LEFT JOIN VALIBLA2.VA_Boutique B1 ON (S1.iSaleBoutiqueId = B1.iBoutiqueId)

WHERE R1.iLinked = 1
AND R2.iSegment2010 >= 40
AND B1.iNtype < 3 
GROUP BY 1,2
HAVING CALCULATED Turnover >0;
QUIT;

%MEND week;

%week(date_deb = '19JUN2017'd, date_week=intnx("day",&date_deb,7*&week_nb.,"sameday"))

when I set week_nb =1, output is as following:

Boutique	Type	nb_client	Turnover
BTQ1	Repetears	1	638

when I set week_nb =2, output is as following:

Boutique	Type	nb_client	Turnover
BTQ1	Repetears	3	2648

And the question is:

How can I save each macro %week output as a sas data set so that I can stack them together and form a new sataset including week1-12?

Re: SAVE %MACRO OUTPUT

PaigeMiller — Mon, 24 Jul 2017 13:23:57 GMT

You can simply append the data sets to save the results using PROC APPEND.

Please put reasonable carriage returns in your code so we can actually read it.

Re: SAVE %MACRO OUTPUT

Kurt_Bremser — Mon, 24 Jul 2017 13:32:26 GMT

Just a hint:

although it looks as one could edit the contents of a code subwindow directly from the main posting window, adding new lines won't work. Place the cursor in the code, click the "little running man" button again, and enter your new lines in the pop-up window.

Concur with @PaigeMiller, just add a proc append step; you can do that in a %do loop insinde the macro that loops week_nb from 1 to 12.

Something like this:


%MACRO week(date_deb=,max_week=);
%do week_nb = 1 %to &max_week;
PROC SQL;
CREATE TABLE Client_int AS
SELECT
  B1.sName as Boutique,
  R0.pop as Type,
  COUNT(DISTINCT R0.ID) as nb_client,
  SUM(S1.fTotalPrice) as Turnover
  FROM save.liste_clients R0
  INNER JOIN VALIBLA2.VA_NmsRecipient R1
  ON (R0.ID = R1.iRecipientId)
  INNER JOIN VALIBLA2.VA_ClientSegmentChina R2
  ON (R0.ID = R2.iRecipientId)
  LEFT JOIN VALIBLA2.va_sale S1
  ON (
    R1.iRecipientId = S1.iSaleRecipientId AND
    &date_deb. <= datepart(S1.tsDate) <= intnx("day",&date_deb,7*&week_nb.,"sameday") AND
    S1.iSaleBoutiqueId IN (1345,1355) AND
    S1.fTotalPrice > 0
  )
  LEFT JOIN VALIBLA2.VA_Boutique B1
  ON (S1.iSaleBoutiqueId = B1.iBoutiqueId)
  WHERE R1.iLinked = 1 AND R2.iSegment2010 >= 40 AND B1.iNtype < 3
  GROUP BY 1,2
  HAVING CALCULATED Turnover >0
;
QUIT;

proc append base=Client_base data=Client_int;
run;
%end;
%MEND week;

%week(date_deb = '19JUN2017'd,max_week=12)

Re: SAVE %MACRO OUTPUT

viviwu — Mon, 24 Jul 2017 14:59:18 GMT

Thank you KurtBremser, that's exactly what I want!