Macro error mystery

[JediApprentice]

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.

[RW9 · Accepted solution]

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.

[ballardw]

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.

[JediApprentice]

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.

[ballardw]

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.