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.
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?
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.
Perhaps this:
data want; set have; by notsorted activity_id activity_code; if first.activity_code then want=1; else want+1; run;
I appreciate the help, but this just numbered the observations and never restarted at 1.
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.
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.
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;
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.