topic Re: Proc SQL Custom Sorting in SAS Programming

Proc SQL Custom Sorting

johnjinkim — Tue, 25 Jul 2017 19:11:05 GMT

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

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.

Re: Proc SQL Custom Sorting

HB — Tue, 25 Jul 2017 19:41:17 GMT


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.

Re: Proc SQL Custom Sorting

johnjinkim — Wed, 26 Jul 2017 12:10:36 GMT

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

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

Re: Proc SQL Custom Sorting

ChrisBrooks — Wed, 26 Jul 2017 12:24:56 GMT

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;

Re: Proc SQL Custom Sorting

johnjinkim — Wed, 26 Jul 2017 12:32:14 GMT

Thank you!