Need first 10 percent of higher buyer, 30Percent is medium buyer, rest is lower buyer

[ManoharNath]

Hi Guys,

I need some info about how to get first 10percent of higher buyer in market table

suppose i have 1000 cust_id and its trans_date , amount, yearmonth variables are there.

in thousand coustomers i need only first 10 percent of higher buyer, Please suggest on it.

By below code i got the tot_amnt by cust_id wise.

proc sql;

create table test as
select cust_id, sum(amount) as tot_amnt from Market
group by cust_id
order by cust_id;
quit;

Thanks,

Manohar

Cust_Id	Trans_Date	Amount	Yearmonth
1111	1/20/2015	100	201501
1111	1/30/2015	200	201501
1111	2/9/2015	300	201502
1111	2/19/2015	500	201502
1111	3/1/2015	800	201503
1111	3/11/2015	900	201503
1111	3/21/2015	700	201503
2222	1/22/2015	150	201501
2222	1/30/2015	180	201501
2222	2/7/2015	210	201502
2222	2/15/2015	240	201502
2222	2/23/2015	270	201502
2222	3/3/2015	300	201503
2222	3/11/2015	330	201503
3333	1/21/2015	190	201501
3333	1/28/2015	290	201501
3333	2/4/2015	390	201502
3333	2/11/2015	490	201502
3333	2/18/2015	590	201502
3333	2/25/2015	690	201502
3333	3/4/2015	790	201503
3333	3/11/2015	890	201503

[user24feb · Accepted solution]

I think this would be a lot easier with proc univariate. Find examples here:

https://support.sas.com/documentation/cdl/en/procstat/63104/HTML/default/viewer.htm#procstat_univari...

Or try (it counts from the bottom and I there is too little data in the sample to give 10-percent-steps):

Data Have;
  Input Cust_Id Trans_Date:MMDDYY10. Amount Yearmonth; 
  Format Trans_Date MMDDYY10.;
  Datalines; 
1111 1/20/2015 100 201501 
1111 1/30/2015 200 201501 
1111 2/9/2015 300 201502 
1111 2/19/2015 500 201502 
1111 3/1/2015 800 201503 
1111 3/11/2015 900 201503 
1111 3/21/2015 700 201503 
2222 1/22/2015 150 201501 
2222 1/30/2015 180 201501 
2222 2/7/2015 210 201502 
2222 2/15/2015 240 201502 
2222 2/23/2015 270 201502 
2222 3/3/2015 300 201503 
2222 3/11/2015 330 201503 
3333 1/21/2015 190 201501 
3333 1/28/2015 290 201501 
3333 2/4/2015 390 201502 
3333 2/11/2015 490 201502 
3333 2/18/2015 590 201502 
3333 2/25/2015 690 201502 
3333 3/4/2015 790 201503 
3333 3/11/2015 890 201503 
;

Proc Univariate Data=Have NoPrint;
  By Cust_Id;
  Var Amount;
  Output Out=Want PctlPts=60 90 100 PctlName=Low_60 Medium_90 Top_100 PctlPre=_;
Run;

[ManoharNath]

Thanks for it 🙂