topic Create serial number variable in SAS Programming

Create serial number variable

JJG — Tue, 25 Apr 2023 15:05:36 GMT

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

Re: Create serial number variable

Tom — Tue, 25 Apr 2023 15:12:12 GMT

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

Re: Create serial number variable

JJG — Tue, 25 Apr 2023 15:24:56 GMT

Thank you very much Tom!