DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

[ Edited ]

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

Accepted Solutions
Solution
‎11-25-2015 07:45 AM
Super Contributor
Posts: 336

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

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;

View solution in original post


All Replies
Solution
‎11-25-2015 07:45 AM
Super Contributor
Posts: 336

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

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;
Occasional Contributor
Posts: 10

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

Thanks for it Smiley Happy

 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 184 views
  • 1 like
  • 2 in conversation