Help using Base SAS procedures

Syntax Error with Macros

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Syntax Error with Macros

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'

          intoSmiley Tongueurchases

      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


Accepted Solutions
Solution
‎02-29-2012 08:03 PM
PROC Star
Posts: 7,364

Syntax Error with Macros

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;

View solution in original post


All Replies
Solution
‎02-29-2012 08:03 PM
PROC Star
Posts: 7,364

Syntax Error with Macros

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;

Frequent Contributor
Posts: 90

Syntax Error with Macros

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.

Super User
Posts: 5,093

Syntax Error with Macros

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.

Frequent Contributor
Posts: 90

Syntax Error with Macros

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

Frequent Contributor
Posts: 90

Syntax Error with Macros

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!!

Super User
Posts: 5,093

Syntax Error with Macros

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 426 views
  • 3 likes
  • 3 in conversation