BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
davidnamh
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag

View solution in original post

5 REPLIES 5
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
davidnamh
Calcite | Level 5

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
Amethyst | Level 16

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

Thanks,
Jag
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
davidnamh
Calcite | Level 5

Thank you so much  Jag. It works fine !

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 895 views
  • 3 likes
  • 2 in conversation