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
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.
PG
Slight cosmetic modif by PG
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.
PG
Slight cosmetic modif by PG
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?
union all simply concatenates the tables. The second tables is the single Total line at the bottom.
glad I could help.
PG
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.