BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SThomas
Calcite | Level 5

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

11 REPLIES 11
ballardw
Super User

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?

SThomas
Calcite | Level 5

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.

ballardw
Super User

Perhaps this:

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

run;
SThomas
Calcite | Level 5

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

ballardw
Super User

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
SThomas
Calcite | Level 5

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.

ballardw
Super User

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.

Shmuel
Garnet | Level 18

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;

SThomas
Calcite | Level 5

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.

Shmuel
Garnet | Level 18

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;

SThomas
Calcite | Level 5

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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