## assign counter conditionally

Solved
Occasional Contributor
Posts: 10

# assign counter conditionally

Hello,

I have the following data set:

 activity id activity code activity date 1 1 11/1/16 12:00 PM 1 2 11/2/16 12:00 PM 1 1 11/3/16 12:00 PM 1 2 11/4/16 12:00 PM 2 1 11/2/16 12:00 PM 2 2 11/2/16 12:00 PM 2 2 11/3/16 12:00 PM 3 1 11/2/16 12:00 PM 3 1 11/2/16 12:00 PM

I want the following data set, which is the same as above except for an additional column:

 activity id activity code activity date Want 1 1 11/1/16 12:00 PM 1 1 2 11/2/16 12:00 PM 1 1 1 11/3/16 12:00 PM 1 1 2 11/4/16 12:00 PM 1 2 1 11/2/16 12:00 PM 1 2 2 11/2/16 12:00 PM 1 2 2 11/3/16 12:00 PM 2 3 1 11/2/16 12:00 PM 1 3 1 11/2/16 12:00 PM 2

Any suggestions? I am using SAS Enterprise Guide version 7.1.

Accepted Solutions
Solution
‎12-07-2016 09:11 AM
Super User
Posts: 12,325

## Re: assign counter conditionally

Please show the exact code you ran. For your example data it incremented. I ignored the date as it had no purpose in demostrating the the count.

Obs activity_id activity_code want
1 1 1 1
2 1 2 1
3 1 1 1
4 1 2 1
5 2 1 1
6 2 2 1
7 2 2 2
8 3 1 1
9 3 1 2
10 4 1 1
11 4 1 2
12 4 1 3

All Replies
Super User
Posts: 12,325

## Re: assign counter conditionally

Exactly what is the logic for assigning the value of the counter? I might guess that you are counting sequential values of the activity code but you should state that explicitly. Also is there a potential of more than 2? Is the count within activity id?

Occasional Contributor
Posts: 10

## Re: assign counter conditionally

Thanks for responding. The purpose is to apply a counter to observations within the same activity id and activity code, while the observations are sorted by activity id, then activity date, then activity code; the counter is more than one only if the same activity id and activity code appear sequentially. There is the possibility of more than two matching id/code obervations when such observations occur sequentially once sorted.

Here's the data set that includes "more than 2" in the want column.

 activity id activity code activity date Want 1 1 11/1/16 12:00 PM 1 1 2 11/2/16 12:00 PM 1 1 1 11/3/16 12:00 PM 1 1 2 11/4/16 12:00 PM 1 2 1 11/2/16 12:00 PM 1 2 2 11/2/16 12:00 PM 1 2 2 11/3/16 12:00 PM 2 3 1 11/2/16 12:00 PM 1 3 1 11/2/16 12:00 PM 2 4 1 11/2/16 12:00 AM 1 4 1 11/3/16 12:00 AM 2 4 1 11/4/16 12:00 AM 3

Thanks again.

Super User
Posts: 12,325

## Re: assign counter conditionally

Perhaps this:

```data want;
set have;
by notsorted activity_id  activity_code;
if first.activity_code then want=1;
else want+1;

run;```
Occasional Contributor
Posts: 10

## Re: assign counter conditionally

I appreciate the help, but this just numbered the observations and never restarted at 1.

Solution
‎12-07-2016 09:11 AM
Super User
Posts: 12,325

## Re: assign counter conditionally

Please show the exact code you ran. For your example data it incremented. I ignored the date as it had no purpose in demostrating the the count.

Obs activity_id activity_code want
1 1 1 1
2 1 2 1
3 1 1 1
4 1 2 1
5 2 1 1
6 2 2 1
7 2 2 2
8 3 1 1
9 3 1 2
10 4 1 1
11 4 1 2
12 4 1 3
Occasional Contributor
Posts: 10

## Re: assign counter conditionally

Weird, but this exact code worked today. I copied and pasted the code both times, changing only the data set names. I'm at a loss, but I am so grateful it worked! Thank you.

Super User
Posts: 12,325

## Re: assign counter conditionally

Sort order would significantly change the result of this bit of code. So if the data used had been sorted some time that might be a cause, or possibly a re-used data set name with similar variables but different content which I've done to myself when testing things.

Posts: 1,699

## Re: assign counter conditionally

data want;

set have;

by activity_id activity_date activity_code;  /* assumed data is already sorted */

if first.activiti_code then want = 1;

else want+1;

run;

Occasional Contributor
Posts: 10

## Re: assign counter conditionally

Thanks, but this one only incremented the counter when all three variables (activity_id activity_date activity_code) were exactly the same from one line to the next. I want to increment the counter when activity_id and activity_code are the same from one line to the next, while the data is sorted by activity_id activity_date activity_code.

Posts: 1,699

## Re: assign counter conditionally

[ Edited ]

Do you mean:

data want;

set have;

by activity_id activity_date activity_code;  /* assumed data is already sorted */

retain want prev_act_code;

if first.activity_ID then do;

want = 1;

prev_act_code = activity_code;

end;

else if activity_code = prev_act_code then want+1;

run;

Occasional Contributor
Posts: 10

## Re: assign counter conditionally

This incremented the counter any time an activity_ID and activity_code pair was repeated. I want it to increment only when they are repeated one after the other when the data is sorted by activity_ID activity_date activity_code.

☑ This topic is solved.