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 |
I think this would be a lot easier with proc univariate. Find examples here:
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;
I think this would be a lot easier with proc univariate. Find examples here:
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;
Thanks for it 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.