topic Re: Summerising Data in New SAS User

Summerising Data

SOOKHUI99 — Sat, 20 Jun 2020 14:32:42 GMT

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.

Re: Summerising Data

ed_sas_member — Sat, 20 Jun 2020 14:54:36 GMT

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;

Re: Summerising Data

SOOKHUI99 — Sat, 20 Jun 2020 15:43:06 GMT

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;

Re: Summerising Data

ed_sas_member — Sat, 20 Jun 2020 15:58:42 GMT

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,

Re: Summerising Data

SOOKHUI99 — Sat, 20 Jun 2020 16:05:05 GMT

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

Re: Summerising Data

ed_sas_member — Sat, 20 Jun 2020 16:07:16 GMT

Hi @SOOKHUI99

You're very welcome ! 😊