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