Assign different name at first instance and second instance

[bharath86]

Hi, 

I have data like this. 

Id	month
1	0
1	0
1	1
1	1
1	3
1	3
2	9
2	9
2	6
2	6

and i want an output like this. 

Id	month	desired_output
1	0	first
1	0	second
1	1	first
1	1	second
1	3	first
1	3	second
2	9	first
2	9	second
2	6	first
2	6	second

I want to assign name 'first' for first record of id=1 and month=0, similarly i want to assign name 'second' for second record of id=1 and month=0.

month will always have two instances, so for every first instance of month i want to assign 'first' and for every second instance of month i want to assign 'second'.

please advise. 

[PaigeMiller]

---

@bharath86 wrote:

month will always have two instances, so for every first instance of month i want to assign 'first' and for every second instance of month i want to assign 'second'. 

---

Assuming this is literally true, EVERY month has EXACTLY two records, then

data want;
    set have;
    if mod(_n_,2)=1 then desired_output=1;
    else desired_output=2;
run;

This tests to see if the record is an odd number, in which case desired_output=1 and if it is an even number then desired_output=2.

Make your code simple, use 1 and 2 rather than 'First' and 'Second'

[ballardw · Accepted solution]

---

@bharath86 wrote:

Hi, 

I have data like this. 

Id	month
1	0
1	0
1	1
1	1
1	3
1	3
2	9
2	9
2	6
2	6

and i want an output like this. 

Id	month	desired_output
1	0	first
1	0	second
1	1	first
1	1	second
1	3	first
1	3	second
2	9	first
2	9	second
2	6	first
2	6	second

I want to assign name 'first' for first record of id=1 and month=0, similarly i want to assign name 'second' for second record of id=1 and month=0.

month will always have two instances, so for every first instance of month i want to assign 'first' and for every second instance of month i want to assign 'second'.

please advise. 

---

This works for your shown example:

data have;
   input Id 	month;
datalines;
1 	0
1 	0
1 	1
1 	1
1 	3
1 	3
2 	9
2 	9
2 	6
2 	6
;

data want;
   set have;
   by id notsorted month;
   length desired $ 6;
   if first.month then desired='First';
   else if last.month then desired='Second';
run;

Warning: If any of the "month" values appear more than twice for a given Id then "Second" will appear on the LAST one and those between will be blank. This also will have "First" if there is only one.

This works because when you use a BY statement then SAS creates automatic variables indicating the first or last value of the group. These variables are 1/0 numeric values which SAS will treat as True/False for logic. Part of the reason I used "else if" is because if there is only one in the group then both First and Last are true.

Since your values for month are not in numeric order the NOTSORTED option on the by statement is needed to use BY group processing.

Note the data step to create example data. That is the preferred way to show your existing data. I may make choices for example data that do not match your data, such as variable types, that could result in my code working for my example data but not your data. So if you provide a working data step then we have data like yours to test code against.