proc sql doubt

[newbie]

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

[Reeza]

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]

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]

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]

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;