Hello!
I have a dataset on which I need to create a "visit" variable (want) with serial numbers according to all available not missing values in another variable (var). The dataset is sorted by ID and Timepoint variables. Below are the 3 variables in the dataset (ID, Timepoint and Var) and last column is what I am looking for (want).
ID | TimePoint | Var | Want |
1 | 1 | . | . |
1 | 2 | 5 | 1 |
1 | 3 | . | . |
1 | 4 | 7 | 2 |
1 | 5 | 4 | 3 |
2 | 1 | . | . |
2 | 2 | 9 | 1 |
2 | 5 | . | . |
3 | 1 | . | . |
3 | 2 | 8 | 1 |
3 | 3 | 3 | 2 |
3 | 4 | 5 | 3 |
3 | 5 | . | . |
3 | 6 | 5 | 4 |
I have tried the following code without success on getting serial number variable (see code and output below):
proc sort data=have;
by ID TimePoint;
run;
data want;
set have;
want + 1;
if Var > 0 then want = 1;
else want = .;
run;
ID | TimePoint | Have | Want |
1 | 1 | . | . |
1 | 2 | 5 | 1 |
1 | 3 | . | . |
1 | 4 | 7 | 1 |
1 | 5 | 4 | 1 |
2 | 1 | . | . |
2 | 2 | 9 | 1 |
2 | 5 | . | . |
3 | 1 | . | . |
3 | 2 | 8 | 1 |
3 | 3 | 3 | 1 |
3 | 4 | 5 | 1 |
3 | 5 | . | . |
3 | 6 | 5 | 1 |
Thank you for any input regarding this matter!
JJG
To have those gaps in the WANT value you need to make another variable. Otherwise there is no place to remember the previous value.
data have ;
input ID TimePoint Var Expect;
cards;
1 1 . .
1 2 5 1
1 3 . .
1 4 7 2
1 5 4 3
2 1 . .
2 2 9 1
2 5 . .
3 1 . .
3 2 8 1
3 3 3 2
3 4 5 3
3 5 . .
3 6 5 4
;
data want;
set have;
by id ;
if first.id then counter=0;
if not missing(var) then do;
counter + 1;
want=counter;
end;
run;
Results
Time OBS ID Point Var Expect counter want 1 1 1 . . 0 . 2 1 2 5 1 1 1 3 1 3 . . 1 . 4 1 4 7 2 2 2 5 1 5 4 3 3 3 6 2 1 . . 0 . 7 2 2 9 1 1 1 8 2 5 . . 1 . 9 3 1 . . 0 . 10 3 2 8 1 1 1 11 3 3 3 2 2 2 12 3 4 5 3 3 3 13 3 5 . . 3 . 14 3 6 5 4 4 4
To have those gaps in the WANT value you need to make another variable. Otherwise there is no place to remember the previous value.
data have ;
input ID TimePoint Var Expect;
cards;
1 1 . .
1 2 5 1
1 3 . .
1 4 7 2
1 5 4 3
2 1 . .
2 2 9 1
2 5 . .
3 1 . .
3 2 8 1
3 3 3 2
3 4 5 3
3 5 . .
3 6 5 4
;
data want;
set have;
by id ;
if first.id then counter=0;
if not missing(var) then do;
counter + 1;
want=counter;
end;
run;
Results
Time OBS ID Point Var Expect counter want 1 1 1 . . 0 . 2 1 2 5 1 1 1 3 1 3 . . 1 . 4 1 4 7 2 2 2 5 1 5 4 3 3 3 6 2 1 . . 0 . 7 2 2 9 1 1 1 8 2 5 . . 1 . 9 3 1 . . 0 . 10 3 2 8 1 1 1 11 3 3 3 2 2 2 12 3 4 5 3 3 3 13 3 5 . . 3 . 14 3 6 5 4 4 4
Thank you very much Tom!
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.