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;
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.