BookmarkSubscribeRSS Feed
newbie
Calcite | Level 5

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

4 REPLIES 4
Reeza
Super User

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;

newbie
Calcite | Level 5

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..

Reeza
Super User

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.

Linlin
Lapis Lazuli | Level 10

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;

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
  • 4 replies
  • 823 views
  • 1 like
  • 3 in conversation