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.
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;
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.
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
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;
Thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.