Syntax Error with Macros

[InfoAlisaA]

Hello Everyone,

I am running into an issue with a program that I am trying to create.

The program is to sum up all of the totals for each country, use a macro to create a new column Purchases, and then produce a report that sorts all of the data descending based upon the Purchases column.

This is what I have so far:

%let Purchases=sum(Total_Retail_Price); /*Total_Retail_Price is found in orion.Order_Fact*/

proc sql;

select distinct c.Country, &Purchases label='Purchases'

  from orion.Order_Fact as o,

       orion.Customer as c

  where o.Customer_ID=c.Customer_ID and

        year(Order_Date)=2007

  having &Purchases

  group by Country

  order by &Purchases;

quit;

The two bolded areas are where I am getting a sytax error.

Here is my code that does not perform the sort, but still works:

proc sql;

title '2007 Purchases by Country';

   select distinct c.Country,

          sum(o.Total_Retail_Price) label='Purchases'

          into:Purchases

      from orion.Order_Fact as o,

           orion.Customer as c

      where o.Customer_ID=c.Customer_ID and

            year(Order_Date) =2007

      group by Country;

quit;

Could someone take a look at this code and let me know where I am going wrong with it?

Thanks!

Alisa

[art297 · Accepted solution]

Not sure what you intended for your 'having' clause but, hopefully, the following will show you how you might be able to correct your code:

%let Purchases=sum(height); /*Total_Retail_Price is found in orion.Order_Fact*/

proc sql;

select distinct c.age, &Purchases as purchases label='Purchases'

  from sashelp.class (keep=age sex weight) as o,

       sashelp.class (keep=age height) as c

    where o.age=c.age

     and sex in ('F','M')

       group by c.age

         having &purchases > 1

           order by calculated Purchases

  ;

quit;

[InfoAlisaA]

Hi Art,

So I modified my code to be like yours in this way:

%let Purchases=sum(Total_Retail_Price);

proc sql;

title '2007 Purchases by Country';

   select distinct c.Country,

          &Purchases label='Purchases'

      from orion.Order_Fact as o,

           orion.Customer as c

      where o.Customer_ID=c.Customer_ID and

            year(Order_Date) =2007

      group by c.Country

      having &Purchases>1

      order by calculated &Purchases;

quit;

This unfortunately still does not work.

Here is the error I get from the log:

1328  %let Purchases=sum(Total_Retail_Price);

1329  proc sql;

1330  title '2007 Purchases by Country';

1331     select distinct c.Country,

1332            &Purchases label='Purchases'

1333        from orion.Order_Fact as o,

1334             orion.Customer as c

1335        where o.Customer_ID=c.Customer_ID and

1336              year(Order_Date) =2007

1337        group by c.Country

1338        having &Purchases>1

1339        order by calculated &Purchases;

NOTE: Line generated by the macro variable "PURCHASES".

1      sum(Total_Retail_Price)

          -

          22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <,

              <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET,

              LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

So I am not sure I am going wrong somewhere with how I typed this in.

[Astounding]

Alisa,

Looks like Art made one more change that you didn't incorporate yet.

Examine how he transferred the calculation to a variable:

&purchases as purchases

Good luck.

[InfoAlisaA]

Thanks for pointing out where I went wrong. Works like a charm now. :smileygrin:

[InfoAlisaA]

Hi Art!

Thanks once again for your help!

Here's my final code:

%let Purchases=sum(Total_Retail_Price);

proc sql;

title '2007 Purchases by Country';

   select distinct c.Country,

          &Purchases as Purchases label='Purchases'

      from orion.Order_Fact as o,

           orion.Customer as c

      where o.Customer_ID=c.Customer_ID and

            year(Order_Date)=2007

            group by c.Country

            having &Purchases>1

      order by calculated Purchases desc;

quit;

After Astounding pointed out my earlier booboo, everything runs fine. :smileygrin:

Thanks!!

[Astounding]

Alisa,

Art has the right idea here.  I just wanted to take the bird's-eye-view for a moment.

Why are you using macro language to solve this problem?  Do you need to run several different analyses, with different values for &PURCHASES?

You will need to picture what the code looks like in SAS, once macro language has generated the program.  For example, you might expect to encounter problems with these clauses if you coded them without macro language:

having  sum(Total_Retail_Price)

order by sum(Total_Retail_Price)

In your example, that's what SAS sees.  Art has shown you what to change, to generate the proper SQL code.

Good luck.