topic Syntax Error with Macros in SAS Procedures

Syntax Error with Macros

InfoAlisaA — Thu, 01 Mar 2012 00:42:58 GMT

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

Syntax Error with Macros

art297 — Thu, 01 Mar 2012 01:03:58 GMT

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;

Syntax Error with Macros

Astounding — Thu, 01 Mar 2012 17:00:39 GMT

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.

Syntax Error with Macros

InfoAlisaA — Thu, 01 Mar 2012 20:18:52 GMT

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)

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.

Syntax Error with Macros

Astounding — Thu, 01 Mar 2012 21:01:28 GMT

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.

Syntax Error with Macros

InfoAlisaA — Thu, 01 Mar 2012 22:41:54 GMT

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

Syntax Error with Macros

InfoAlisaA — Thu, 01 Mar 2012 22:42:46 GMT

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