topic How to create total lines in Proc SQL in SAS Procedures

How to create total lines in Proc SQL

InfoAlisaA — Tue, 20 Mar 2012 19:57:44 GMT

Hello Everyone,

So I have the following problem where I have this code, and I need to create total lines (not footnotes) at the bottom of my report to show the totals for Retail Price and quantity.

This is the code I have so far:

proc sql;

select avg(Total_Retail_Price) into:Price

from orion.order_fact

where "&start"d<order_date<"&stop"d;

quit;

proc sql;

select avg(Quantity) format=comma4.2

into:Quant

from orion.order_fact

where "&start"d<order_date<"&stop"d;

quit;

proc sql;

title1 "Report from &start to &stop";

title3 "Average Quantity: &quant";

title4 "Average Price: &price";

select Order_ID, Order_Date, Quantity, Total_Retail_Price format=dollar5.

from orion.order_fact

where "&start"d<order_date<"&stop"d;

quit;

Now I know in a proc print statement to get my total lines I need to just use sum and then whatever my variable is.

I am just not sure how to do something like that with a PROC SQL statement.

Could someone out there let me know how to do this?

Thanks!

Alisa

Re: How to create total lines in Proc SQL

PGStats — Tue, 20 Mar 2012 20:15:42 GMT

Try this:

proc sql noprint;

select avg(Total_Retail_Price), avg(Quantity) format=comma4.2

into :Price, :Quant

from orion.order_fact

where "&start"d < order_date < "&stop"d;

quit;

proc sql;

title1 "Report from &start to &stop";

title3 "Average Quantity: &quant";

title4 "Average Price: &price";

(select Order_ID, Order_Date, Quantity, Total_Retail_Price format=dollar5.

from orion.order_fact

where "&start"d < order_date < "&stop"d)

union all

(select "Total", ., sum(Quantity), sum(Total_Retail_Price)

from orion.order_fact

where "&start"d < order_date < "&stop"d);

quit;

if Order_ID is numeric, then use put(Order_ID, best.) in the query.

Slight cosmetic modif by PG

How to create total lines in Proc SQL

InfoAlisaA — Thu, 22 Mar 2012 16:40:46 GMT

Awesome! This worked out perfectly. I did have to take out your parenthesis because SAS did not want to run the program with those in. How does the union all create total lines at the bottom of the file?

How to create total lines in Proc SQL

PGStats — Thu, 22 Mar 2012 16:45:34 GMT

union all simply concatenates the tables. The second tables is the single Total line at the bottom.

glad I could help.