topic re-assign values for consecutive obs in SAS Procedures

re-assign values for consecutive obs

deleted_user — Mon, 21 Oct 2013 22:01:08 GMT

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!

Re: re-assign values for consecutive obs

ballardw — Mon, 21 Oct 2013 22:05:29 GMT

What would the desired output be for:

K1234T 2012 12

K1234T 2013 1

Consecutive months across the year boundary?

Re: re-assign values for consecutive obs

deleted_user — Mon, 21 Oct 2013 22:10:23 GMT

My data only have year 2012, no other year.

Thank you!

Re: re-assign values for consecutive obs

Haikuo — Tue, 22 Oct 2013 00:18:22 GMT

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

Re: re-assign values for consecutive obs

deleted_user — Wed, 23 Oct 2013 16:29:31 GMT

Haikuo, thank you very much!