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.
Its telling you that you have unalanced quote marks and that whole chunk of text is being read as sting, not code. As for where that is happening, well thats up to you to debug. I would suggest to start by checking out what is happening before the macro definition as the probelm has occured before that. Note, good idea to re-start session as these things hold over to next runs of code. Say you are missing a quote in run one, then it will still affect any other runs thereafter, making debugging difficult.
So, start a new session. Then take each block of code out of that macro, and run it individually, ensure that it works. If all the blocks of code work, then it could also be your parameters, some of which I can see are going to be a problem:
maclists=('MAC01','MACNC','MACIA','MACIVL','MACMAILHIM','MACMS'),
Doesn't look good to start with. Also, I don't see why you have all those SQL steps, there should be simpler methods using SAS functionality. Also, not sure on some of your syntax, what are you specifying a literal for datasets?
A=WORK.'MAC JXA'n,
That doesn't make any sense? Why do you have spaces in it? Looks like the whole process is a bit shaky.
Its telling you that you have unalanced quote marks and that whole chunk of text is being read as sting, not code. As for where that is happening, well thats up to you to debug. I would suggest to start by checking out what is happening before the macro definition as the probelm has occured before that. Note, good idea to re-start session as these things hold over to next runs of code. Say you are missing a quote in run one, then it will still affect any other runs thereafter, making debugging difficult.
So, start a new session. Then take each block of code out of that macro, and run it individually, ensure that it works. If all the blocks of code work, then it could also be your parameters, some of which I can see are going to be a problem:
maclists=('MAC01','MACNC','MACIA','MACIVL','MACMAILHIM','MACMS'),
Doesn't look good to start with. Also, I don't see why you have all those SQL steps, there should be simpler methods using SAS functionality. Also, not sure on some of your syntax, what are you specifying a literal for datasets?
A=WORK.'MAC JXA'n,
That doesn't make any sense? Why do you have spaces in it? Looks like the whole process is a bit shaky.
Set
OPTIONS MPRINT SYMBOLGEN;
and rerun the code for one of the macro calls.
I suspect that when you trace down the source of the
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks
warning that you'll see something that is commenting cause issues with either the Proc statement or Quit appearing inside of quotes.
Did you try commenting out all but the first Proc sql and see if that has a result? and then add in each Proc Sql step until the output stops?
I also suspect the heavy use of the name literals complicates things.
Thank you for the suggestions. I tried debugging and still couldn't find the errors so did what @RW9 mentioned which was to restart the session and everything worked, producing all datasets properly with no errors. Basically I inherited this code and tried to make it simpler by turning it intro a macro instead of having 24 proc sqls I only have 8. Do you guys have any general suggestions for how to make this even simpler using SAS functionality instead of using all these proc sql's that feed into each other? I'm not expecting you to give me code, just maybe how I could approach the problem.
I'm not going to try to figure out what the code is actually supposed to accomplish. When looking at lots of sums though I start suspecting that maybe Proc Summary/Means and creative use of classes may be an option. You can get summaries for each level and combination of levels of multiple variables in one pass through Proc Summary and then use the automatic _type_ variable to sort through them.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: