topic Re: Proc Sort question in SAS Data Science

Proc Sort question

Dougfoley2 — Sat, 24 Mar 2018 21:21:27 GMT

I am new to SAS so my question might be simple but i can not find anything on the internet to answer it so that is why I am asking it. I have some data that I am doing a proc sort on. In the data the information is organized randomly so I am doing my initial sort on the employee_id. Then i am using a proc print to sum the profits of individual employees based on their id. to this extent now i need the profit to be sorted by the total of their sales so highest profit generator to the top and in a descending manner. Can you help?

Thanks

Re: Proc Sort question

art297 — Sat, 24 Mar 2018 21:51:46 GMT

Sounds like you have at least employee_id and some_profit_variable. Couldn't you just sort by:

by employee_id descending some_profit_variable;

Art, CEO, AnalystFinder.com

Re: Proc Sort question

Dougfoley2 — Sat, 24 Mar 2018 22:30:12 GMT

The profit is based on transactions so the proc print is generating the sum for each employee. At this step is when i would need to restructure the data generated to make the total profit from the individual transactions in a descending manner.

Re: Proc Sort question

Astounding — Sat, 24 Mar 2018 23:40:11 GMT

Do you need to print each individual sale, or do you need to print just the total of all sales per employee_id?

Re: Proc Sort question

Tom — Sun, 25 Mar 2018 04:51:03 GMT

PROC PRINT is just a method to display data.

If you want to calculate sums then use PROC SUMMARY. With PROC SUMMARY you might be able to avoid the original sort and just use a CLASS statement instead of a BY statement.

proc summary data=have nway ;
class employee_id ;
var profit ;
output out=want sum= ;
run;
proc sort data=want ; by descending profit ; run;
proc print;
 var profit employee_id;
run;

(or possible roll your own by writing a query using PROC SQL).

proc sql ;
  create table want as
      select employee_id,sum(profit) as profit 
      group by 1
      order by 2 desc
  ;
quit;

You could also look at using PROC FREQ and trick it into make a sum by using your PROFIT variable as a WEIGHT variable. But watch out if you have negative or zero values for profit.

proc freq data=have order=freq ;
  weight profit ;
  tables employee_id;
run;