topic proc sql doubt in SAS Procedures

proc sql doubt

newbie — Mon, 18 Mar 2013 17:28:44 GMT

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

Re: proc sql doubt

Reeza — Mon, 18 Mar 2013 17:50:32 GMT

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;

Re: proc sql doubt

Linlin — Mon, 18 Mar 2013 17:55:04 GMT

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;

Re: proc sql doubt

newbie — Mon, 18 Mar 2013 18:14:53 GMT

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

Re: proc sql doubt

Reeza — Mon, 18 Mar 2013 18:48:49 GMT

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.