topic Re: ranking in SAS Programming

ranking

davidnamh — Sun, 23 Mar 2014 04:27:16 GMT

Hi, I would to turn "have" dataset into "want" dataset

I would like to give it rank by descending order of t_date within each account (Rank_acct_tdate)

Could you help me how to do that ? Thanks

1) amount should not be 0 or ".";

2) sort by account and t_date in descending order

3) rank by t_date within each account.

;

data have;

infile datalines delimiter=',';

input account amount t_date $;

datalines;

345,.,1/1/2014

345,23,1/4/2014

345,10,1/5/2014

345,5,1/8/2014

678,.,2/2/2013

678,4,2/5/2013

678,55,2/9/2013

;

run;

data want;

infile datalines delimiter=',';

input account amount t_date $ Rank_acct_tdate;

datalines;

345,5,1/8/2014,1

345,10,1/5/2014,2

345,23,1/4/2014,3

345,.,1/1/2014,.

678,55,2/9/2013,1

678,4,2/5/2013,2

678,.,2/2/2013,.

;

run;

Re: ranking

Jagadishkatam — Sun, 23 Mar 2014 04:50:20 GMT

Please try proc rank procedure

proc sort data=have;

by account descending amount;

run;

proc rank data=have descending out=want;

ranks Rank_acct_tdate;;

var amount;

by account ;

run;

Thanks,

Jag

Re: ranking

davidnamh — Sun, 23 Mar 2014 06:11:24 GMT

Hi Jag,

Thank you for the reply. However, the result didn't come out as I wanted to..

I would to rank by rank t_date (descending order) within same account. but it looks like it ranked by amount. I just don't want to give a rank if amount is 0 or missing. so I tried with

proc sort data=have;

by t_date descending t_date;

run;

proc rank data=have descending out=want;

ranks Rank_acct_date;;

var t_date;

by account ;

run;

but it doesn't work..

Re: ranking

Jagadishkatam — Sun, 23 Mar 2014 06:24:46 GMT

Please try the below code, it gave the desired output.

proc sort data=have;

by account descending t_date;

run;

proc rank data=have descending out=want;

ranks Rank_acct_date;

var t_date;

by account ;

run;

data want_;

set want;

if amount eq . then Rank_acct_date=.;

run;

Thanks,

Jag

Re: ranking

Jagadishkatam — Sun, 23 Mar 2014 06:32:03 GMT

Alternatively by datastep

proc sort data=have;

by account descending t_date;

run;

data want;

set have;

by account descending t_date;

retain Rank_acct_date;

if first.account then Rank_acct_date=1;

else Rank_acct_date+1;

if amount eq . then Rank_acct_date= .;

run;

Thanks,

Jag

Re: ranking

davidnamh — Sun, 23 Mar 2014 06:52:47 GMT

Thank you so much Jag. It works fine !