Proc SQL Custom Sorting

[johnjinkim]

I am seeking to sort by the product (but with the highest paid first along with their stratifications).

data have ;

    input product $ type $ paid : dollar. ;

    cards ;

       apple new $12

       banana old $5

       apple old $5

       apple old $5

       banana new $6

       banana new $7

;

 Initially used this basic SQL:

proc sql ;
    select product, type, sum(paid) as totalpaid
    from have
    group by product, type
    order by totalpaid desc ;
run ;

But obviously only orders by total paid, without any stratification for the different products.

This is the result I am seeking is the highest total amount regardless of type (apple's total is $22 vs. banana's $18; $10 for old and $12 for new), then stratified by old then new categories. Hoping it's a simple order command:

product        type       totalpaid    

  apple          old        $10

  apple          new       $12

  banana       old        $5

  banana       new       $13

Thanks in advance.

[HB]

proc sql;
create table myNewData as
select product, type, sum(paid) as totalpaid
    from have
    group by product, type;
run;

proc sort data= myNewData;
	by product descending type totalpaid ; 
run;

proc print data=myNewData; 
	var product type totalpaid;
run;

gives me

                              Obs    product    type    totalpaid

                               1     apple      old         10
                               2     apple      new         12
                               3     banana     old          5
                               4     banana     new         13

I think

order by product, type desc, totalpaid;

in your SQL would work as well.

[johnjinkim]

Hi,

Correct in that this gives the result I asked for but not for the correct reason.

data have ;

    input product $ type $ paid : dollar. ;

    cards ;

       apple new $12

       banana old $5

       apple old $5

       apple old $5

       banana new $6

       banana new $7

       kiwi old $22

       kiwi new $16

;

Perhaps a better example would be the above, with a desired result of the below. Kiwi as a whole is $38 so the highest product grouping total. But I'd like it listed first, with the same sorting for type (old, then new). Thanks for your efforts!

product        type       totalpaid    

  kiwi             old         $22

  kiwi             new        $16

  apple          old        $10

  apple          new       $12

  banana       old        $5

  banana       new       $13

[ChrisBrooks · Accepted solution]

I think this gives you what you need

proc sql;
	create table want1
	as select product, type, paid, sum(paid) as producttot
	from have
	group by product;	
quit;

proc sql;
	create table want2
	as select distinct product, type, producttot, sum(paid) as totalpaid
	from want1
	group by product, type
	order by producttot desc, product, type desc
	;
	alter table want2
	drop producttot;
quit;

[johnjinkim]

Thank you!