ranking

[davidnamh]

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;

[Jagadishkatam]

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

[davidnamh]

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..

[Jagadishkatam]

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

[Jagadishkatam · Accepted solution]

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

[davidnamh]

Thank you so much  Jag. It works fine !