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?
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)
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.
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)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.