BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
johnjinkim
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisBrooks
Ammonite | Level 13

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

4 REPLIES 4
HB
Barite | Level 11 HB
Barite | Level 11

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
Obsidian | Level 7

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
Ammonite | Level 13

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
Obsidian | Level 7

Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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