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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.