topic Re: Assign status in the group in SAS Programming

Assign status in the group

kumarK — Thu, 14 May 2015 18:55:50 GMT

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:

ID	Dept	Count	Status
101	1	10	third
101	2	50	first
101	3	25	second
102	1	30	second
102	2	25	third
102	3	35	first
103	1	40	first
103	2	25	second
103	3	10	third

Thanks.

Re: Assign status in the group

Reeza — Thu, 14 May 2015 19:11:43 GMT

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?

Re: Assign status in the group

kumarK — Thu, 14 May 2015 19:15:43 GMT

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;

Re: Assign status in the group

Haikuo — Thu, 14 May 2015 19:16:56 GMT

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;

Re: Assign status in the group

kumarK — Thu, 14 May 2015 19:25:18 GMT

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

Re: Assign status in the group

Reeza — Thu, 14 May 2015 19:28:22 GMT

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;

Re: Assign status in the group

Reeza — Thu, 14 May 2015 19:30:10 GMT

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

Re: Assign status in the group

ballardw — Thu, 14 May 2015 19:31:08 GMT

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

Re: Assign status in the group

Ksharp — Fri, 15 May 2015 14:33:47 GMT

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;