Help using Base SAS procedures

re-assign values for consecutive obs

Accepted Solution Solved
Reply
N/A
Posts: 0
Accepted Solution

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
Respected Advisor
Posts: 3,124

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

View solution in original post


All Replies
Super User
Posts: 10,460

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?

N/A
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
Respected Advisor
Posts: 3,124

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

N/A
Posts: 0

Re: re-assign values for consecutive obs

Haikuo, thank you very much!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 239 views
  • 0 likes
  • 3 in conversation