SAVE %MACRO OUTPUT

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

SAVE %MACRO OUTPUT

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:

BoutiqueTypenb_clientTurnover
BTQ1Repetears1638

 

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

BoutiqueTypenb_clientTurnover
BTQ1Repetears32648

 

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? 

 


Accepted Solutions
Solution
‎07-24-2017 10:58 AM
Super User
Posts: 7,868

Re: SAVE %MACRO OUTPUT

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)
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Trusted Advisor
Posts: 1,934

Re: SAVE %MACRO OUTPUT

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.

Solution
‎07-24-2017 10:58 AM
Super User
Posts: 7,868

Re: SAVE %MACRO OUTPUT

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)
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: SAVE %MACRO OUTPUT

Posted in reply to KurtBremser
Thank you KurtBremser, that's exactly what I want!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 135 views
  • 0 likes
  • 3 in conversation