Can some one help me with the below case. I want to get the sum of prod- Apple as one row and sum of all prods (Apple+Bannan+Cane)as another in the final output, i want this to be implemented only using proc sql. Sample code i tried as below though it doesnt work..Please help .
data test;
input prod$ sold;
cards;
Apple 10
Apple 20
Banana 20
Cane 40
;
run;
proc sql;
select case prod when 'Apple' then 'Apple'
else 'Fruits' end as newprod ,sum(cost) as summ from test
group by newprod;
quit;
Expected output:
newprod summ
Apple 30
Fruits 90
both solutions are a bit 'hardcoded'. Someone may have a more generic solution.
I will say that this would be a perfect use of the MultiLabel Format and Proc Means or Summary, and probably faster than the solution below.
/*in the format you wanted*/
proc sql;
create table want as
Select * from
(select a.prod, sum(a.sold) as summ
from test a
where a.prod='Apple'
union
select 'Fruits' as prod, sum(b.sold) as summ from test b) c;
quit;
/*in a single row that you can then flip*/
proc sql;
create table want as
select sum((prod='Apple')*sold) as apple_total, sum(sold) as overall_total
from test;
quit;
Thanks Reeza and Linlin for your reply. Can the expected output not be got in a single query without using union..Im working to get it in a single query so request u to share your ideas on that..
I don't know how you can in SQL, as mentioned before, a multilabel format within proc means is better in my opinion.
Usually you won't summarize just by apple, you'll want all 3 with subtotals and then an overall total. Really it depends on what you're trying to do with it.
Just guessing, but it may be better to create a summarized table that summarizes all fruits with a total and then filter that for a report.
proc sql;
select prod as newprod,sum(sold) as summ
from test where upcase(prod)='APPLE'
union
select "Fruits" as newprod,sum(sold) as summ
from test;
quit;
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.