topic assign counter conditionally in SAS Programming

assign counter conditionally

SThomas — Tue, 06 Dec 2016 18:06:04 GMT

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.

Thank you, in advance, for your help.

Re: assign counter conditionally

ballardw — Tue, 06 Dec 2016 18:16:04 GMT

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?

Re: assign counter conditionally

SThomas — Tue, 06 Dec 2016 18:56:57 GMT

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.

Re: assign counter conditionally

ballardw — Tue, 06 Dec 2016 19:07:22 GMT

Perhaps this:

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

run;

Re: assign counter conditionally

Shmuel — Tue, 06 Dec 2016 19:12:34 GMT

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;

Re: assign counter conditionally

SThomas — Tue, 06 Dec 2016 19:34:32 GMT

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

Re: assign counter conditionally

SThomas — Tue, 06 Dec 2016 19:37:14 GMT

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.

Re: assign counter conditionally

Shmuel — Tue, 06 Dec 2016 20:10:45 GMT

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;

Re: assign counter conditionally

ballardw — Tue, 06 Dec 2016 20:13:01 GMT

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

Re: assign counter conditionally

SThomas — Wed, 07 Dec 2016 14:00:43 GMT

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.

Re: assign counter conditionally

SThomas — Wed, 07 Dec 2016 14:10:46 GMT

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.

Re: assign counter conditionally

ballardw — Wed, 07 Dec 2016 16:28:53 GMT

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.