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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.