Help using Base SAS procedures

How to create total lines in Proc SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

How to create total lines in Proc SQL

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) intoSmiley Tonguerice  

   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


Accepted Solutions
Solution
‎03-20-2012 04:15 PM
Respected Advisor
Posts: 4,919

Re: How to create total lines in Proc SQL

Posted in reply to InfoAlisaA

Try this:

proc sql noprint;

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

   into Smiley Tonguerice, :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


All Replies
Solution
‎03-20-2012 04:15 PM
Respected Advisor
Posts: 4,919

Re: How to create total lines in Proc SQL

Posted in reply to InfoAlisaA

Try this:

proc sql noprint;

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

   into Smiley Tonguerice, :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
Frequent Contributor
Posts: 90

How to create total lines in Proc SQL

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?

Respected Advisor
Posts: 4,919

How to create total lines in Proc SQL

Posted in reply to InfoAlisaA

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

glad I could help.

PG

PG
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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