topic Re: How to generate a unique variable to indicate the first office visit in SAS Programming

How to generate a unique variable to indicate the first office visit

CynthiaWei — Tue, 19 Nov 2019 00:10:32 GMT

Hi SAS experts,

I am having a data set with individuals having multiple office visits

This is what I have:

ID Office_visit_date Total_visits

1 4/12/2012 3

1 6/1/2012 3

1 9/19/2013 3

2 5/18/2011 2

2 8/20/2012 2

3 2/22/2011 4

3 6/13/2012 4

3 6/30/2012 4

3 9/15/2013 4

What I want is as this:

ID Office_visit_date Total_visits First_visit subsequent_visit second_visit

1 4/12/2012 3 1 0 0

1 6/1/2012 3 0 1 1

1 9/19/2013 3 0 1 0

2 5/18/2011 2 1 0 0

2 8/20/2012 2 0 1 1

3 2/22/2011 4 1 0 0

3 6/13/2012 4 0 1 1

3 6/30/2012 4 0 1 0

3 9/15/2013 4 0 1 0

Specifically, First_visit, subsequent_visit, and second_visit are the three variables that I want to generate.

Thank you in advance for any help!

Kind regards,

Re: How to generate a unique variable to indicate the first office visit

CynthiaWei — Tue, 19 Nov 2019 00:14:42 GMT

Also, I would like to generate a variable titled which_visit, with 1 indicating the first office visit and 2 indicating the subsequent office visit no matter it is the second, third, or fourth.

Thank you very much!

Kind regards,

Re: How to generate a unique variable to indicate the first office visit

PaigeMiller — Tue, 19 Nov 2019 00:21:33 GMT

data want;
    set have;
    by id;
    if first.id then counter=0;
    counter+1;
run;

This assumes the data set have is sorted properly by id and date.

Once you have variable COUNTER it should be easy to generate variables FIRST_VISIT, SECOND_VISIT and SUBSEQUENT_VISIT.

Re: How to generate a unique variable to indicate the first office visit

CynthiaWei — Tue, 19 Nov 2019 00:37:11 GMT

Hi Paige,

Thank you so much for you prompt reply. The code works perfect! I appreciate it a lot! Big thumb up!!!

Best regards,

Cynthia