I have a data set that I'm trying to add a count variable for. Here is s my dataset:
key | date | type |
1234 | 1/28/2022 | A |
1234 | 2/15/2022 | A |
1234 | 2/28/2022 | A |
1234 | 3/6/2022 | B |
1234 | 4/1/2022 | B |
1234 | 4/15/2022 | C |
1234 | 5/1/2022 | C |
1133 | 12/21/2021 | A |
1133 | 1/20/2022 | A |
1133 | 3/18/2022 | B |
1133 | 4/4/2022 | C |
1133 | 5/3/2022 | C |
3434 | 2/16/2022 | A |
3434 | 3/10/2022 | A |
3434 | 4/3/2022 |
C |
I've tried to use the following code:
data add_count;
set test_data;
count +1;
by type;
if first.type then count=1;
run;
However, this does not work. I get the following error:
ERROR: BY variables are not properly sorted on data set WORK.test_data.
I need to count the type, for each key in the order they are sorted by date. If I add in the "date" to the by statement in the code above, then my count is just 1 for every observation, since the dates are different.
How can I get this output:
key | date | type | count |
1234 | 1/28/2022 | A | 1 |
1234 | 2/15/2022 | A | 2 |
1234 | 2/28/2022 | A | 3 |
1234 | 3/6/2022 | B | 1 |
1234 | 4/1/2022 | B | 2 |
1234 | 4/15/2022 | C | 1 |
1234 | 5/1/2022 | C | 2 |
1133 | 12/21/2021 | A | 1 |
1133 | 1/20/2022 | A | 2 |
1133 | 3/18/2022 | B | 1 |
1133 | 4/4/2022 | C | 1 |
1133 | 5/3/2022 | C | 2 |
3434 | 2/16/2022 | A | 1 |
3434 | 3/10/2022 | A | 2 |
3434 | 4/3/2022 | C | 1 |
Thank you!
You need to tell the data step that the you want the BY statement to work even though the data is NOT sorted by those variables.
Use the NOTSORTED keyword.
data add_count;
set test_data;
by type notsorted;
count +1;
if first.type then count=1;
run;
If you want the numbering to reset also when KEY changes that add that variable to the BY statement also.
Try this:
by key notsorted type;
This assumes that all values of KEY are consecutive, and that all values of TYPE are consecutive.
You need to tell the data step that the you want the BY statement to work even though the data is NOT sorted by those variables.
Use the NOTSORTED keyword.
data add_count;
set test_data;
by type notsorted;
count +1;
if first.type then count=1;
run;
If you want the numbering to reset also when KEY changes that add that variable to the BY statement also.
Wow, that was so easy.. Thank you, that worked perfectly!
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.