DATA Step, Macro, Functions and more

Proc SQL Custom Sorting

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Proc SQL Custom Sorting

[ Edited ]

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.


Accepted Solutions
Solution
‎07-26-2017 08:32 AM
Super Contributor
Posts: 439

Re: Proc SQL Custom Sorting

Posted in reply to johnjinkim

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;

View solution in original post


All Replies
Regular Contributor
Regular Contributor
Posts: 156

Re: Proc SQL Custom Sorting

[ Edited ]
Posted in reply to johnjinkim

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.

Contributor
Posts: 29

Re: Proc SQL Custom Sorting

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

Solution
‎07-26-2017 08:32 AM
Super Contributor
Posts: 439

Re: Proc SQL Custom Sorting

Posted in reply to johnjinkim

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;
Contributor
Posts: 29

Re: Proc SQL Custom Sorting

Posted in reply to ChrisBrooks

Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 165 views
  • 1 like
  • 3 in conversation