Here is my macro with the macro calls: %macro fvs(date1=,date2=,maclists=,businessline=,from=,A=,B=,C=,D=,E=,F=,channel=,final=);
PROC SQL;
CREATE TABLE &A AS
SELECT t1.MonthPeriod,
t1.GPI,
/* COUNT_of_MonthPeriod */
(COUNT(t1.MonthPeriod)) AS COUNT_of_MonthPeriod,
/* AVG_of_MAC_Price */
(AVG(t1.MAC_Price)) FORMAT=BEST20.4 AS AVG_of_MAC_Price,
/* Avg Price */
(IFN((COUNT(t1.MonthPeriod))>=2, (AVG(t1.MAC_Price)), ., .)) FORMAT=BEST20.4 AS 'Avg Price'n
FROM &from t1
WHERE t1.MonthPeriod IN
(
&date1,
&date2
)
GROUP BY t1.MonthPeriod,
t1.GPI;
QUIT;
PROC SQL;
CREATE TABLE &B AS
SELECT t1.GPI,
t1.'Avg Price'n FORMAT=BEST20.4 AS 'Old MAC'n
FROM &A t1
WHERE t1.MonthPeriod IN
(
&date1
);
QUIT;
PROC SQL;
CREATE TABLE &C AS
SELECT t1.GPI,
t1.'Avg Price'n FORMAT=BEST20.4 AS 'New MAC'n
FROM &A t1
WHERE t1.MonthPeriod IN
(
&date2
);
QUIT;
PROC SQL;
CREATE TABLE &D AS
SELECT /* GPI */
(COALESCEC(t1.GPI,t2.GPI)) AS GPI,
t2.'New MAC'n FORMAT=20.4 AS 'New MAC'n,
t1.'Old MAC'n FORMAT=20.4 AS 'Old MAC'n
FROM &B t1
FULL JOIN WORK.'MAC 01C'n t2 ON (t1.GPI = t2.GPI);
QUIT;
PROC SQL;
CREATE TABLE &E AS
SELECT DISTINCT t4.CLIENT,
t4.Business_Line,
t4.Channel,
t1.GPI,
t1.'New MAC'n,
t1.'Old MAC'n,
t2.Y_AWP,
t3.Discount,
/* SUM_of_SUM_of_SUM_OF_QUANTITYDIS */
(SUM(t4.SUM_of_SUM_OF_QUANTITYDISPENSED)) FORMAT=20. AS SUM_of_SUM_of_SUM_OF_QUANTITYDIS
FROM &D t1
LEFT JOIN EGTASK.AWP t2 ON (t1.GPI = t2.GPI_ID_Number)
LEFT JOIN EGTASK.GENERICQTY t4 ON (t1.GPI = t4.GPINumber)
LEFT JOIN EGTASK.GENERIC_DISCOUNTS_BY_CLIENT t3 ON (t4.'MAC List'n = t3.'MAC List'n) AND (t4.Business_Line =
t3.Business_Line) AND (t4.CLIENT = t3.CLIENT) AND (t4.Channel = t3.Channel)
WHERE t4.'MAC List'n IN
&maclists AND &businessline
GROUP BY t4.CLIENT,
t4.Business_Line,
t4.Channel,
t1.GPI,
t1.'New MAC'n,
t1.'Old MAC'n,
t2.Y_AWP,
t3.Discount;
QUIT;
PROC SQL;
CREATE TABLE &F AS
SELECT t1.CLIENT,
t1.Business_Line,
t1.Channel,
t1.GPI,
/* New MAC */
(IFN(t1.'New MAC'n>0, t1.'New MAC'n, t1.Y_AWP*(1-t1.Discount), t1.'New MAC'n)) AS 'New MAC'n,
/* Old MAC */
(IFN(t1.'Old MAC'n>0, t1.'Old MAC'n, t1.Y_AWP*(1-t1.Discount), t1.'Old MAC'n)) AS 'Old MAC'n,
/* Difference */
((IFN(t1.'New MAC'n>0, t1.'New MAC'n, t1.Y_AWP*(1-t1.Discount), t1.'New MAC'n))-(IFN(t1.'Old MAC'n>0, t1.
'Old MAC'n, t1.Y_AWP*(1-t1.Discount), t1.'Old MAC'n))) AS Difference,
/* SUM_of_SUM_of_SUM_of_SUM_OF_QUA1 */
(SUM(t1.SUM_of_SUM_of_SUM_OF_QUANTITYDIS)) FORMAT=20. AS SUM_of_SUM_of_SUM_of_SUM_OF_QUA1
FROM &E t1
GROUP BY t1.CLIENT,
t1.Business_Line,
t1.Channel,
t1.GPI,
(CALCULATED 'New MAC'n),
(CALCULATED 'Old MAC'n),
(CALCULATED Difference);
QUIT;
PROC SQL;
CREATE TABLE &channel AS
SELECT t1.Business_Line,
t1.CLIENT,
t1.Channel,
/* (Saving)/Loss */
(SUM(t1.Difference*t1.SUM_of_SUM_of_SUM_of_SUM_OF_QUA1)) FORMAT=DOLLAR25.2 AS '(Saving)/Loss'n
FROM &F t1
GROUP BY t1.Business_Line,
t1.CLIENT,
t1.Channel;
QUIT;
PROC SQL;
CREATE TABLE &final AS
SELECT t1.Business_Line,
t1.CLIENT,
/* (Saving)/Loss */
(SUM(t1.Difference*t1.SUM_of_SUM_of_SUM_of_SUM_OF_QUA1)) FORMAT=DOLLAR25.2 AS '(Saving)/Loss'n
FROM &F t1
GROUP BY t1.Business_Line,
t1.CLIENT;
QUIT;
%mend fvs;
%fvs(date1="2015M07",
date2="2016M07",
maclists=('MAC01','MACNC','MACIA','MACIVL','MACMAILHIM','MACMS'),
businessline=t4.Business_Line ='Commercial',
from=EGTASK.'MAC PRICE TREND BY GPI'n,
A=WORK.'MAC 01A'n,
B=WORK.'MAC 01B'n,
C=WORK.'MAC 01C'n,
D=WORK.'MAC 01D'n,
E=WORK.'MAC 01E'n,
F=WORK.'MAC 01F'n,
channel=WORK.'MAC 01 Channel'n,
final=WORK.'MAC 01 FINAL'n);
%fvs(date1="2015M07",
date2="2016M07",
maclists=('MACMEDCARE'),
businessline=t4.Business_Line ='Medicare',
from=EGTASK.'MAC PRICE TREND BY GPI_0000'n,
A=WORK.'MAC MEDCAREA'n,
B=WORK.'MAC MEDCAREB'n,
C=WORK.'MAC MEDCAREC'n,
D=WORK.'MAC MEDCARED'n,
E=WORK.'MAC MEDCAREE'n,
F=WORK.'MAC MEDCAREF'n,
channel=WORK.'MAC MEDCARE Channel'n,
final=WORK.'MAC MEDCARE FINAL'n);
%fvs(date1="2015M07",
date2="2016M07",
maclists=('MACJX'),
businessline=t4.Business_Line IN ('Commercial','Medicare'),
from=EGTASK.'MAC PRICE TREND BY GPI_0001'n,
A=WORK.'MAC JXA'n,
B=WORK.'MAC JXB'n,
C=WORK.'MAC JXC'n,
D=WORK.'MAC JXD'n,
E=WORK.'MAC JXE'n,
F=WORK.'MAC JXF'n,
channel=WORK.'MAC JX Channel'n,
final=WORK.'MAC JX FINAL'n); And here is the log with error messages: 1 The SAS System 09:33 Friday, September 30, 2016
1 %_eg_hidenotesandsource;
2 ;*';*";*/;quit;run;
3 OPTIONS PAGENO=MIN;
4 %_eg_restorenotesandsource;
5 %_eg_hidenotesandsource;
6
7 %LET _CLIENTTASKLABEL='Program';
8 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
9 %LET _CLIENTPROJECTPATH='J:\shrproj\NETWORK\MAC Team\03 DATAWAREHOUSE\Queries\SAS Queries\FVS\MAC FVS TEST.egp';
10 %LET _CLIENTPROJECTNAME='MAC FVS TEST.egp';
11 %LET _SASPROGRAMFILE=;
12
13 ODS _ALL_ CLOSE;
14 OPTIONS DEV=ACTIVEX;
____________________________________________________________________________________
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
15 GOPTIONS XPIXELS=0 YPIXELS=0;
16 FILENAME EGSR TEMP;
17 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
18 STYLE=HtmlBlue
19 STYLESHEET=(URL="file:///C:/Program%20Files/SAS71/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
19 STYLESHEET=(URL="file:///C:/Program%20Files/SAS71/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css"
___________________________________________________________________________________________
49
19 ! )
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
20 NOGTITLE
21 NOGFOOTNOTE
22 GPATH=&sasworklocation
23 ENCODING=UTF8
24 options(rolap="on")
25 ;
26
27 GOPTIONS ACCESSIBLE;
28 %_eg_restorenotesandsource;
29
30 %macro fvs(date1=,date2=,maclists=,businessline=,from=,A=,B=,C=,D=,E=,F=,channel=,final=);
31 PROC SQL;
32 CREATE TABLE &A AS
33 SELECT t1.MonthPeriod,
34 t1.GPI,
35 /* COUNT_of_MonthPeriod */
36 (COUNT(t1.MonthPeriod)) AS COUNT_of_MonthPeriod,
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
37 /* AVG_of_MAC_Price */
38 (AVG(t1.MAC_Price)) FORMAT=BEST20.4 AS AVG_of_MAC_Price,
39 /* Avg Price */
40 (IFN((COUNT(t1.MonthPeriod))>=2, (AVG(t1.MAC_Price)), ., .)) FORMAT=BEST20.4 AS 'Avg Price'n
41 FROM &from t1
42 WHERE t1.MonthPeriod IN
2 The SAS System 09:33 Friday, September 30, 2016
43 (
44 &date1,
45 &date2
46 )
47 GROUP BY t1.MonthPeriod,
48 t1.GPI;
49 QUIT;
50
51 PROC SQL;
52 CREATE TABLE &B AS
53 SELECT t1.GPI,
54 t1.'Avg Price'n FORMAT=BEST20.4 AS 'Old MAC'n
55 FROM &A t1
56 WHERE t1.MonthPeriod IN
57 (
58 &date1
59 );
60 QUIT;
61
62 PROC SQL;
63 CREATE TABLE &C AS
64 SELECT t1.GPI,
65 t1.'Avg Price'n FORMAT=BEST20.4 AS 'New MAC'n
66 FROM &A t1
67 WHERE t1.MonthPeriod IN
68 (
69 &date2
70 );
71 QUIT;
72
73 PROC SQL;
74 CREATE TABLE &D AS
75 SELECT /* GPI */
76 (COALESCEC(t1.GPI,t2.GPI)) AS GPI,
77 t2.'New MAC'n FORMAT=20.4 AS 'New MAC'n,
78 t1.'Old MAC'n FORMAT=20.4 AS 'Old MAC'n
79 FROM &B t1
80 FULL JOIN WORK.'MAC 01C'n t2 ON (t1.GPI = t2.GPI);
81 QUIT;
82
83 PROC SQL;
84 CREATE TABLE &E AS
85 SELECT DISTINCT t4.CLIENT,
86 t4.Business_Line,
87 t4.Channel,
88 t1.GPI,
89 t1.'New MAC'n,
90 t1.'Old MAC'n,
91 t2.Y_AWP,
92 t3.Discount,
93 /* SUM_of_SUM_of_SUM_OF_QUANTITYDIS */
94 (SUM(t4.SUM_of_SUM_OF_QUANTITYDISPENSED)) FORMAT=20. AS SUM_of_SUM_of_SUM_OF_QUANTITYDIS
95 FROM &D t1
96 LEFT JOIN EGTASK.AWP t2 ON (t1.GPI = t2.GPI_ID_Number)
97 LEFT JOIN EGTASK.GENERICQTY t4 ON (t1.GPI = t4.GPINumber)
98 LEFT JOIN EGTASK.GENERIC_DISCOUNTS_BY_CLIENT t3 ON (t4.'MAC List'n = t3.'MAC List'n) AND (t4.Business_Line =
99 t3.Business_Line) AND (t4.CLIENT = t3.CLIENT) AND (t4.Channel = t3.Channel)
100 WHERE t4.'MAC List'n IN
3 The SAS System 09:33 Friday, September 30, 2016
101 &maclists AND &businessline
102 GROUP BY t4.CLIENT,
103 t4.Business_Line,
104 t4.Channel,
105 t1.GPI,
106 t1.'New MAC'n,
107 t1.'Old MAC'n,
108 t2.Y_AWP,
109 t3.Discount;
110 QUIT;
111
112 PROC SQL;
113 CREATE TABLE &F AS
114 SELECT t1.CLIENT,
115 t1.Business_Line,
116 t1.Channel,
117 t1.GPI,
118 /* New MAC */
119 (IFN(t1.'New MAC'n>0, t1.'New MAC'n, t1.Y_AWP*(1-t1.Discount), t1.'New MAC'n)) AS 'New MAC'n,
120 /* Old MAC */
121 (IFN(t1.'Old MAC'n>0, t1.'Old MAC'n, t1.Y_AWP*(1-t1.Discount), t1.'Old MAC'n)) AS 'Old MAC'n,
122 /* Difference */
123 ((IFN(t1.'New MAC'n>0, t1.'New MAC'n, t1.Y_AWP*(1-t1.Discount), t1.'New MAC'n))-(IFN(t1.'Old MAC'n>0, t1.
124 'Old MAC'n, t1.Y_AWP*(1-t1.Discount), t1.'Old MAC'n))) AS Difference,
125 /* SUM_of_SUM_of_SUM_of_SUM_OF_QUA1 */
126 (SUM(t1.SUM_of_SUM_of_SUM_OF_QUANTITYDIS)) FORMAT=20. AS SUM_of_SUM_of_SUM_of_SUM_OF_QUA1
127 FROM &E t1
128 GROUP BY t1.CLIENT,
129 t1.Business_Line,
130 t1.Channel,
131 t1.GPI,
132 (CALCULATED 'New MAC'n),
133 (CALCULATED 'Old MAC'n),
134 (CALCULATED Difference);
135 QUIT;
136
137 PROC SQL;
138 CREATE TABLE &channel AS
139 SELECT t1.Business_Line,
140 t1.CLIENT,
141 t1.Channel,
142 /* (Saving)/Loss */
143 (SUM(t1.Difference*t1.SUM_of_SUM_of_SUM_of_SUM_OF_QUA1)) FORMAT=DOLLAR25.2 AS '(Saving)/Loss'n
144 FROM &F t1
145 GROUP BY t1.Business_Line,
146 t1.CLIENT,
147 t1.Channel;
148 QUIT;
149 PROC SQL;
150 CREATE TABLE &final AS
151 SELECT t1.Business_Line,
152 t1.CLIENT,
153 /* (Saving)/Loss */
154 (SUM(t1.Difference*t1.SUM_of_SUM_of_SUM_of_SUM_OF_QUA1)) FORMAT=DOLLAR25.2 AS '(Saving)/Loss'n
155 FROM &F t1
156 GROUP BY t1.Business_Line,
157 t1.CLIENT;
24 options(rolap="on")
4 The SAS System 09:33 Friday, September 30, 2016
__
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
158 QUIT;
159 %mend fvs;
160
161
162 %fvs(date1="2015M07"
______________
49
162 ! ,
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
163 date2="2016M07",
164 maclists=('MAC01','MACNC','MACIA','MACIVL','MACMAILHIM','MACMS'),
165 businessline=t4.Business_Line ='Commercial',
166 from=EGTASK.'MAC PRICE TREND BY GPI'n,
167 A=WORK.'MAC 01A'n,
168 B=WORK.'MAC 01B'n,
169 C=WORK.'MAC 01C'n,
170 D=WORK.'MAC 01D'n,
171 E=WORK.'MAC 01E'n,
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
163 date2="2016M07",
__
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
172 F=WORK.'MAC 01F'n,
173 channel=WORK.'MAC 01 Channel'n,
174 final=WORK.'MAC 01 FINAL'n);
175
176 %fvs(date1="2015M07"
______________
49
176 ! ,
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
177 date2="2016M07",
178 maclists=('MACMEDCARE'),
179 businessline=t4.Business_Line ='Medicare',
180 from=EGTASK.'MAC PRICE TREND BY GPI_0000'n,
181 A=WORK.'MAC MEDCAREA'n,
182 B=WORK.'MAC MEDCAREB'n,
183 C=WORK.'MAC MEDCAREC'n,
184 D=WORK.'MAC MEDCARED'n,
185 E=WORK.'MAC MEDCAREE'n,
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
186 F=WORK.'MAC MEDCAREF'n,
177 date2="2016M07",
__
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
5 The SAS System 09:33 Friday, September 30, 2016
between a quoted string and the succeeding identifier is recommended.
187 channel=WORK.'MAC MEDCARE Channel'n,
188 final=WORK.'MAC MEDCARE FINAL'n);
189
190
191 %fvs(date1="2015M07"
______________
49
191 ! ,
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
192 date2="2016M07",
193 maclists=('MACJX'),
194 businessline=t4.Business_Line IN ('Commercial','Medicare'),
195 from=EGTASK.'MAC PRICE TREND BY GPI_0001'n,
196 A=WORK.'MAC JXA'n,
197 B=WORK.'MAC JXB'n,
198 C=WORK.'MAC JXC'n,
199 D=WORK.'MAC JXD'n,
200 E=WORK.'MAC JXE'n,
201 F=WORK.'MAC JXF'n,
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
202 channel=WORK.'MAC JX Channel'n,
203 final=WORK.'MAC JX FINAL'n);
204
205
206 %_eg_hidenotesandsource;
207
208 GOPTIONS NOACCESSIBLE;
209 %LET _CLIENTTASKLABEL=;
210 %LET _CLIENTPROCESSFLOWNAME=;
211 %LET _CLIENTPROJECTPATH=;
212 %LET _CLIENTPROJECTNAME=;
213 %LET _SASPROGRAMFILE=;
214
215 ;*';*";*/;quit;run;
216 ODS _ALL_ CLOSE;
217
218 %_eg_restorenotesandsource;
219
220
221 %_eg_hidenotesandsource;
222 QUIT; RUN;
223 %_eg_restorenotesandsource;
224 As you can see the log doesn't point to any errors - just notes/warnings. However, no datasets are outputted. I don't understand why. IS there something wrong with the macro calls? Any help would be appreciated.
... View more
