Summerising Data

[SOOKHUI99]

My question is to identify the buyer with the smallest and largest total number of item purchase. First I need to sum up the total unit purchase for each same buyer name as the name is repeated. I decided to use proc means but unfortunately, I can't get the output. So what should I do so that I can sum up and get the highest and lowest no of purchase? REMARK: I'm using SAS 9.4

I decided to get the result like this. But the sample syntax in the following picture does not work.

[ed_sas_member]

Hi @SOOKHUI99 

Please supply data in a usable form (DATALINES in a DATA step).

Could you please try the following code and see whether it does meet your needs?

The MEANS Procedure output a dataset names 'Want' from the input dataset 'Have' : it contains one observation per buyer with the total price (= sum price*units)

The PROC SQL output a report which select the buyer with the minimal amount and the buyer with the maximal amount.

best,

proc means data=have noprint nway;
	class Buyer_Name;
	var price;
	weight unit;
	output out=want (drop=_:) sum=sum_purchase;
run;

proc sql;
	select *
	from want
	having sum_purchase= min(sum_purchase) or sum_purchase=max(sum_purchase);
quit;

[SOOKHUI99]

Hi! thanks a lot!! Finally I get the answer. But I got modified a bit. One more thing, want can i do if i want to add the word 'largest' and 'smallest' beside the buyer name or sum purchase. thanks again!!!

data have;
set foodsalesreport6;
run ;
proc means data=have noprint nway;
class Buyer_Name;
var unit;
output out=want (drop=_:) sum=sum_purchase;
run;
proc sql;
select *
from want
having sum_purchase= min(sum_purchase) or sum_purchase=max(sum_purchase);
quit;

[ed_sas_member · Accepted solution]

Hi @SOOKHUI99 

You can simply modify the PROC SQL as follows:

proc sql;
	select "Smallest", *
	from want
	having sum_purchase= min(sum_purchase)
	
	union 
	
	select "Largest", *
	from want
	having sum_purchase= max(sum_purchase);
	
quit;

Best,

[SOOKHUI99]

thanks a lot!!!!! i get it!!!! finally i done my last question for my assignment!!! you are really my angel!!! May God bless you!!

[ed_sas_member]

Hi @SOOKHUI99 

You're very welcome ! 😊