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

Hi Gurus,

I have this dataset. Maximum count could be assigned One second max would be second like on........

The code should be dynamic in the way that accepts any condition.like 2 depts only 10 depts....

data have;

input ID Dept Count ;

cards;

101 1 10

101 2 50

101 3 25

102 1 30

102 2 25

102 3 35

103 1 40

103 2 25

103 3 10

run;

I want this:

IDDeptCountStatus
101110third
101250first
101325second
102130second
102225third
102335first
103140first
103225second
103310third

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

also don't forget format WORDS.

data want;

set have;

by id descending count;

if first.id then rank=1;

else rank+1;

format rank words.;

rank_desc=put(rank, words.);

run;

View solution in original post

8 REPLIES 8
Reeza
Super User

Does it have to be the word first, second, third or can it be 1st, 2nd, 3rd?

What version of SAS do you have and Also what have you tried?

kumarK
Quartz | Level 8

Anything 1st ,2nd or 3rd.

I have 9.2  and tried this way but it is not giving second and third.

proc sql;

create table want as

select id,max(count)as max from have

group by 1;

quit;

Reeza
Super User

You need a custom format.

First create the ranks using either a data step or proc rank and then apply the format.

Create a format for ordinal numbers (1-1st, 2-2nd)

data have;

     input ID Dept Count;

     cards;

101 1 10

101 2 50

101 3 25

102 1 30

102 2 25

102 3 35

103 1 40

103 2 25

103 3 10

;

run;

proc sort data=have; by id descending count;

run;

data want;

set have;

by id descending count;

if first.id then rank=1;

else rank+1;

format rank ordinal_fmt.;

rank_desc=put(rank, ordinal_fmt.);

run;

Ksharp
Super User

also don't forget format WORDS.

data want;

set have;

by id descending count;

if first.id then rank=1;

else rank+1;

format rank words.;

rank_desc=put(rank, words.);

run;

Haikuo
Onyx | Level 15

word such as first, second ect should be avoided in the the data itself, in the case of the reporting, use format. So in this case, PROC RANK comes in handy:

data have;

     input ID Dept Count;

     cards;

101 1 10

101 2 50

101 3 25

102 1 30

102 2 25

102 3 35

103 1 40

103 2 25

103 3 10

run;

proc rank data=have out=want ties=low descending;

     by id;

     var count;

     ranks CountRank;

run;

kumarK
Quartz | Level 8

Is there any alternative way without using procedures in data step or SQL, i have to include some other conditions also.

ballardw
Super User

Generally the approach would be to assign categories based on your values and then create any ranking or analysis by the group variable(s).

Reeza
Super User

@kumarK If you're going to remove questions as well, I don't feel like its worth taking the time to answer them. Make the questions generic enough to not violate company policy.

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
  • 8 replies
  • 1033 views
  • 8 likes
  • 5 in conversation