BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
InfoAlisaA
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

3 REPLIES 3
PGStats
Opal | Level 21

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

PG
InfoAlisaA
Calcite | Level 5

Awesome! Smiley Happy 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?

PGStats
Opal | Level 21

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

glad I could help.

PG

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1136 views
  • 0 likes
  • 2 in conversation