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 🙂
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.