topic Re: Regarding Dates in SAS Procedures

Regarding Dates

raveena — Mon, 18 Jun 2012 19:54:40 GMT

Hi,

In my final output I need to get that if any member is not enrolled for any month from Jan thru June 2012.

For e.g.: I have to create a report till June 30th 2012 for those members who have a gap in enrollment

ABC 20110201 20120229 This means that the member is enrolled from Jan thru Feb 2012

ABC 20120401 20120430 This means that the member is enrolled for April 2012

ABC 20120701 20121231

In this case ABC has a gap for March, May and June 2012

DEF 20120401 20120530

DEF 20120101 20120229

DEF 20120301 20120331

In this case DEF is enrolled for the months from Jan thru May 2012 and is missing June 2012

GHI 20110401 20120430

GHI 20120101 20120229

GHI 20120301 20120331

GHI 20120501 20120930

In this case GHI is enrolled for ALL the months from Jan thru Sep 2012 and is not needed in the final output

Please let me know how to get a missing months on the final output.

Thanks.

Re: Regarding Dates

Alpay — Mon, 18 Jun 2012 21:29:19 GMT

There is some overlapping of periods (GHI 20110401 20120430,GHI 20120101 20120229,GHI 20120301 20120331) and there is one place where 1 gap in coverage exists (DEF 20120401 20120530)

Assuming that the patient is covered for a given month if he has coverage on the first day of that month.

data x;

input PatID $ Start_Date anydtdte. End_Date anydtdte.;

datalines;

ABC 20110201 20120229

ABC 20120401 20120430

ABC 20120701 20121231

DEF 20120401 20120530

DEF 20120101 20120229

DEF 20120301 20120331

GHI 20110401 20120430

GHI 20120101 20120229

GHI 20120301 20120331

GHI 20120501 20120930

;

run;

proc sort data=x; by PatId; run;

data want;

array m [0:5];

do i=0 to 5;

m =0;

end;

do until(last.PatId);

set x;

by PatID;

if Start_Date <= '30Jun2012'd or End_Date >= '01Jan2012'd then do;

Cnt = INTCK('month',Start_Date,End_Date);

do i=0 to Cnt;

Month = INTCK('month','01Jan2012'd,INTNX('month',Start_Date,i));

if Month >=0 and Month <= 5 then m[Month]=1;

end;

do i=0 to 5;

if m = 0 then do;Gap_Month = INTNX('month','01Jan2012'd,i);output;end;

end;

format gap_Month yymmd7.;

keep PatId Gap_Month;

run;

Re: Regarding Dates

Ksharp — Tue, 19 Jun 2012 07:22:49 GMT

What output do you want?

data x;
input PatID $ Start_Date : anydtdte. End_Date : anydtdte.;
format Start_Date End_Date yymmddn8.;
datalines;
ABC 20120201 20120229
ABC 20120401 20120430
ABC 20120701 20121231
DEF 20120401 20120530
DEF 20120101 20120229
DEF 20120301 20120331
GHI 20120401 20120430
GHI 20120101 20120229
GHI 20120301 20120331
GHI 20120501 20120930
;
run;

proc sort data=x;  by PatId Start_Date; run;
data want;
 merge x x(keep=PatId Start_Date rename=(PatId=_id Start_Date=_start) firstobs=2);
 output;
 if PatId=_id and End_Date+1 ne _start then do;
    Start_Date=intnx('month',End_Date,1,'b');
    End_Date=intnx('month',_start,-1,'e');
    output;
 end;
 drop _id _start;
run;

Ksharp

Re: Regarding Dates

Astounding — Tue, 19 Jun 2012 17:59:40 GMT

I don't know if "simplifying" the code makes it better or clearer, but here's an attempt. Using the Ksharp code all the way through the PROC SORT, then:

data want (keep=PatID gap_begins_date gap_ends_date);

set x;

by PatID;

last_end_date = lag(end_date);

if first.PatID then do;

if start_date > '01Jan2012'd then do;

gap_begins_date = '01Jan2012'd;

gap_ends_date = start_date - 1;

output;

end;

else if start_date > last_end_date + 1 then do;

gap_begins_date = last_end_date + 1;

gap_ends_date = start_date - 1;

output;

end;

if last.PatID and end_date < '30jun2012'd then do;

gap_begins_date = end_date + 1;

gap_ends_date = '30jun2012'd;

output;

end;

run;

It will measure gaps to the day, not just the month. Beauty is in the eye of the beholder, I guess.

Re: Regarding Dates

raveena — Wed, 20 Jun 2012 15:13:11 GMT

Thanks for all suggestions.