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
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;
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;
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.
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.
Thanks for pointing out where I went wrong. Works like a charm now. :smileygrin:
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!!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.