Creating visit number for longitudinal data

[mt88]

Hello,

I'm trying to create a visit number variable for a longitudinal data set that is formatted long. My data set has an ID and a visit date, for example:

ID	Visit Date
6960	8/30/2016
11073	6/8/2016
11093	8/23/2016
11129	9/28/2017
11274	4/13/2016
11274	10/10/2016
11274	5/8/2017
11288	11/30/2017
11315	9/25/2017
11315	2/27/2018
11315	8/21/2018
11322	8/8/2017
11453	1/27/2017
11453	8/15/2017
11453	1/29/2018
11453	7/17/2018
11533	4/14/2016
11533	9/7/2017
11533	6/13/2018

I'd like to end up with this:

ID	Visit Date	visit
6960	8/30/2016	1
11073	6/8/2016	1
11093	8/23/2016	1
11129	9/28/2017	1
11274	4/13/2016	1
11274	10/10/2016	2
11274	5/8/2017	3
11288	11/30/2017	1
11315	9/25/2017	1
11315	2/27/2018	2
11315	8/21/2018	3
11322	8/8/2017	1
11453	1/27/2017	1
11453	8/15/2017	2
11453	1/29/2018	3
11453	7/17/2018	4
11533	4/14/2016	1
11533	9/7/2017	2
11533	6/13/2018	3

I used this code:

proc sort data = dataset;
by ID Visit Date;
run;

data dataset2;
set dataset;

by ID;
visit+1;
if first.ID then visit = 1;

run;

I ended up with this:

ID	Visit Date	visit
6960	8/30/2016	1
11073	6/8/2016	2
11093	8/23/2016	3
11129	9/28/2017	1
11274	4/13/2016	2
11274	10/10/2016	3
11274	5/8/2017	4
11288	11/30/2017	5
11315	9/25/2017	1
11315	2/27/2018	2
11315	8/21/2018	3
11322	8/8/2017	4
11453	1/27/2017	1
11453	8/15/2017	2
11453	1/29/2018	3
11453	7/17/2018	4
11533	4/14/2016	5
11533	9/7/2017	6
11533	6/13/2018	7

Any advise would be very helpful.

Thank you very much.

[novinosrin · Accepted solution]

Try

proc sort data = dataset;
by ID Visit Date;
run;

data dataset2;
set dataset;

by ID;
if first.ID then visit = 1;

else visit+1;

run;

[mt88]

Thank you for your response. I adjusted the code as you suggested, but I got the same result.

[novinosrin]

Hi @mt88   Here is a test using the sample you posted

data have;
input ID	VisitDate :mmddyy10.;
format visitdate mmddyy10.;
cards;
6960	8/30/2016
11073	6/8/2016
11093	8/23/2016
11129	9/28/2017
11274	4/13/2016
11274	10/10/2016
11274	5/8/2017
11288	11/30/2017
11315	9/25/2017
11315	2/27/2018
11315	8/21/2018
11322	8/8/2017
11453	1/27/2017
11453	8/15/2017
11453	1/29/2018
11453	7/17/2018
11533	4/14/2016
11533	9/7/2017
11533	6/13/2018
;

data want;
set have;
by id;
if first.id then visit=1;
else visit+1;
run;

proc print noobs;run;

ID	VisitDate	visit
6960	08/30/2016	1
11073	06/08/2016	1
11093	08/23/2016	1
11129	09/28/2017	1
11274	04/13/2016	1
11274	10/10/2016	2
11274	05/08/2017	3
11288	11/30/2017	1
11315	09/25/2017	1
11315	02/27/2018	2
11315	08/21/2018	3
11322	08/08/2017	1
11453	01/27/2017	1
11453	08/15/2017	2
11453	01/29/2018	3
11453	07/17/2018	4
11533	04/14/2016	1
11533	09/07/2017	2
11533	06/13/2018	3

[cminard]

data temp;
input ID VisitDate:mmddyy10.;
format VisitDate mmddyy10.;
datalines;
6960 8/30/2016
11073 6/8/2016
11093 8/23/2016
11129 9/28/2017
11274 4/13/2016
11274 10/10/2016
11274 5/8/2017
11288 11/30/2017
11315 9/25/2017
11315 2/27/2018
11315 8/21/2018
11322 8/8/2017
11453 1/27/2017
11453 8/15/2017
11453 1/29/2018
11453 7/17/2018
11533 4/14/2016
11533 9/7/2017
11533 6/13/2018
;
run;

proc sort data=temp; by id visitdate; run;
data temp2;
set temp;
by id;
if first.id then VisitNumber=1;
else VisitNumber+1;
run;

[Astounding]

This is not a valid name for a variable:

Visit date

So the program you are presenting is not plausible unless ....

A likely cause of the results you describe: perhaps your original data set already contains a variable named VISIT. In that case, the results will be thrown off because the program modifies an existing variable instead of creating a new variable.