DATA Step, Macro, Functions and more

assign counter conditionally

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

assign counter conditionally

Hello,

 

I have the following data set:

activity idactivity codeactivity date
1111/1/16 12:00 PM
1211/2/16 12:00 PM
1111/3/16 12:00 PM
1211/4/16 12:00 PM
2111/2/16 12:00 PM
2211/2/16 12:00 PM
2211/3/16 12:00 PM
3111/2/16 12:00 PM
3111/2/16 12:00 PM

 

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

activity idactivity codeactivity dateWant
1111/1/16 12:00 PM1
1211/2/16 12:00 PM1
1111/3/16 12:00 PM1
1211/4/16 12:00 PM1
2111/2/16 12:00 PM1
2211/2/16 12:00 PM1
2211/3/16 12:00 PM2
3111/2/16 12:00 PM1
3111/2/16 12:00 PM2

 

 

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

Thank you, in advance, for your help.

 


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

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

View solution in original post


All Replies
Super User
Posts: 10,497

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 idactivity codeactivity dateWant
1111/1/16 12:00 PM1
1211/2/16 12:00 PM1
1111/3/16 12:00 PM1
1211/4/16 12:00 PM1
2111/2/16 12:00 PM1
2211/2/16 12:00 PM1
2211/3/16 12:00 PM2
3111/2/16 12:00 PM1
3111/2/16 12:00 PM2
4111/2/16 12:00 AM1
4111/3/16 12:00 AM2
4111/4/16 12:00 AM3

 

Thanks again.

Super User
Posts: 10,497

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: 10,497

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: 10,497

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.

Trusted Advisor
Posts: 1,374

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.

Trusted Advisor
Posts: 1,374

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 299 views
  • 0 likes
  • 3 in conversation