DATA Step, Macro, Functions and more

Solved
Occasional Contributor
Posts: 10

[ 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: 355

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;``````

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

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

Thanks for it

🔒 This topic is solved and locked.