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;
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
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
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..
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
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
Thank you so much Jag. It works fine !
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.