Solved
Not applicable
Posts: 0

# re-assign values for consecutive obs

Hello,

I'm a beginner of SAS. Could anyone help me out? Thank you!

Here is a sample dataset. I have data with patientID, year and month.

PatientID  Year Month
K1234T     2012  1
K1234T     2012  2
K1234T     2012  3
K1234T     2012  8
M2350Y    2012  7
I4656Y     2012   5

I4656Y     2012   10

The final dataset I need is, in the final dataset, if a patient has consecutive month, then
just keep one record and create earlist month and latest month columns (e.g. for K1234T,min_Month=1 ,max_Month=3).If not consecutive month, min_Month=max_Month=Month for this record.

So the final dataset would like:
PatientID  Year min_Month max_Month
K1234T     2012  1          3
K1234T     2012  8          8
M2350Y     2012  7          7
I4656Y     2012  5          5

I4656Y     2012  10       10

Thank you!

Accepted Solutions
Solution
‎10-21-2013 08:18 PM
Posts: 3,167

## Re: re-assign values for consecutive obs

This works for your presented data:

data have;

input patientid :\$8. year month;

cards;

K1234T     2012  1

K1234T     2012  2

K1234T     2012  3

K1234T     2012  8

M2350Y    2012  7

I4656Y     2012   5

I4656Y     2012   10

;

data want;

do until (last.patientid);

set have;

by patientid notsorted;

if first.patientid then do; min=month; max=month; _mon=month;end;

else if month-_mon=1 then do; max=month; _mon=month;end;

else if month-_mon>1 then do; output; min=month; max=month; _mon=month;end;

if last.patientid then output;

end;

drop month _:;

run;

However, if you have more than one year of data, then you need

1) Concatenated year and month to make a SAS date variable

2) Use intck() to get the difference.

Haikuo

All Replies
Super User
Posts: 13,517

## Re: re-assign values for consecutive obs

What would the desired output be for:

K1234T     2012  12

K1234T     2013  1

Consecutive months across the year boundary?

Not applicable
Posts: 0

## Re: re-assign values for consecutive obs

My data only have  year 2012, no other year.

Thank you!

Solution
‎10-21-2013 08:18 PM
Posts: 3,167

## Re: re-assign values for consecutive obs

This works for your presented data:

data have;

input patientid :\$8. year month;

cards;

K1234T     2012  1

K1234T     2012  2

K1234T     2012  3

K1234T     2012  8

M2350Y    2012  7

I4656Y     2012   5

I4656Y     2012   10

;

data want;

do until (last.patientid);

set have;

by patientid notsorted;

if first.patientid then do; min=month; max=month; _mon=month;end;

else if month-_mon=1 then do; max=month; _mon=month;end;

else if month-_mon>1 then do; output; min=month; max=month; _mon=month;end;

if last.patientid then output;

end;

drop month _:;

run;

However, if you have more than one year of data, then you need

1) Concatenated year and month to make a SAS date variable

2) Use intck() to get the difference.

Haikuo

Not applicable
Posts: 0

## Re: re-assign values for consecutive obs

Haikuo, thank you very much!

🔒 This topic is solved and locked.