Hello,
This is the dataset that I have:
Obs | ID | _NAME_ | COL1 |
1 | 100 | month1 | . |
2 | 100 | month2 | 1 |
3 | 100 | month3 | 1 |
4 | 100 | month4 | 1 |
5 | 100 | month5 | . |
6 | 100 | month6 | . |
7 | 100 | month7 | . |
8 | 100 | month8 | . |
9 | 200 | month1 | . |
10 | 200 | month2 | |
11 | 200 | month3 | |
12 | 200 | month4 | |
13 | 200 | month5 | . |
14 | 200 | month6 | 1 |
15 | 200 | month7 | 1 |
16 | 200 | month8 | 1 |
I would like to end up with this dataset:
Obs | ID | _NAME_ | COL1 | count |
1 | 100 | month1 | . | . |
2 | 100 | month2 | 1 | 1 |
3 | 100 | month3 | 1 | 2 |
4 | 100 | month4 | 1 | 3 |
5 | 100 | month5 | . | . |
6 | 100 | month6 | . | . |
7 | 100 | month7 | . | . |
8 | 100 | month8 | . | . |
9 | 200 | month1 | . | . |
10 | 200 | month2 | . | |
11 | 200 | month3 | . | |
12 | 200 | month4 | . | |
13 | 200 | month5 | 1 | 1 |
14 | 200 | month6 | 1 | 2 |
15 | 200 | month7 | 1 | 3 |
16 | 200 | month8 | 1 | 4 |
Thank you very much in advance!
Like this?
data WANT;
set HAVE;
if ID ne lag(ID) then COUNT=0;
if COL1 = 1 then COUNT+COL1;
else COUNT=.;
run;
Like this?
data WANT;
set HAVE;
if COL1 then COUNT+COL1;
else COUNT=.;
run;
Hi @ChrisNZ,
I'm afraid that I might have spoken too soon. In my data, I have instances such as so when I use the coding that you suggested to me:
Obs | ID | _NAME_ | COL1 | count |
1 | 100 | month1 | . | . |
2 | 100 | month2 | 1 | 1 |
3 | 100 | month3 | 1 | 2 |
4 | 100 | month4 | 1 | 3 |
5 | 100 | month5 | 1 | 4 |
6 | 100 | month6 | 1. | 5. |
7 | 100 | month7 | 1. | 6. |
8 | 100 | month8 | 1. | 7 |
9 | 200 | month1 | 1. | 8 |
10 | 200 | month2 | 1 | 9 |
11 | 200 | month3 | . | |
12 | 200 | month4 | . | |
13 | 200 | month5 | 1 | 1 |
14 | 200 | month6 | 1 | 2 |
15 | 200 | month7 | 1 | 3 |
16 | 200 | month8 | 1 | 4 |
However, I need it to look like this:
Obs | ID | _NAME_ | COL1 | count |
1 | 100 | month1 | . | . |
2 | 100 | month2 | 1 | 1 |
3 | 100 | month3 | 1 | 2 |
4 | 100 | month4 | 1 | 3 |
5 | 100 | month5 | 1 | 4 |
6 | 100 | month6 | 1 | 5 |
7 | 100 | month7 | 1 | .6 |
8 | 100 | month8 | 1 | .7 |
9 | 200 | month1 | 1 | .1 |
10 | 200 | month2 | 1 | .2 |
11 | 200 | month3 | . | |
12 | 200 | month4 | . | |
13 | 200 | month5 | 1 | 1 |
14 | 200 | month6 | 1 | 2 |
15 | 200 | month7 | 1 | 3 |
16 | 200 | month8 | 1 | 4 |
I would like to have it numbering restarted once the id changes. I have tried this code but it does not work: Would you have any additional suggestion for me? Thank you.
data want;
set have;
by id;
if COL1 = 1 then count+col;
else col1 = .;
run;
Like this?
data WANT;
set HAVE;
if ID ne lag(ID) then COUNT=0;
if COL1 = 1 then COUNT+COL1;
else COUNT=.;
run;
Or another way:
data WANT;
set HAVE;
retain COUNT;
COUNT=ifn(missing(COL1) , .
,ifn(ID ne lag(ID) , 1
, sum(COUNT,1)));
run;
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.