Regarding Gap month

[raveena]

Hi All,

Please help me out to get the below logic,

Membno             GRPNUM                 EFFDAT                 EXPDAT                  GAP

ABC                        LI                          20120201                20120201                  0

ABC                        H1NAS                  20120201                20120331

ABC                        PAMAS                 20120401                99999999

SO the member ABC is not enrolled for the month of Jan and we don’t want this Memb to be included

DEF                        LI                          20120101                20120229                2

DEF                        H1NAS                  20120401                20120430

DEF                        PAMAS                 20120601                99999999

SO the member DEF we need him for he has a GAP in enrollment for the month of March and May. So total GAP = 2

GHI                        LI                          20120201                20120229               1

GHI                        H1NAS                  20120301                20120331

GHI                        PAMAS                 20120401                20120430

GHI                        PAMAS                 20120601                20120630

For the Member GHI, we need him as he has a GAP in enrollment for the month of May, so GAP = 1

JKL                          LI                          20120401                20120430               0

JKL                          H1NAS                  20120501                20120531

JKL                          PAMAS                 20120601                99999999

For the Member JKL, we don’t need him as he has NO GAP even though he is not enrolled for Jan thru March

MNO                     LI                           20120101                20120331                  0

MNO                     H1NAS                  20120401                 20120430

For the Member MNO, we don’t need him as his enrollment ended in April and he is no longer enrolled

We need ONLY those Members who have a GAP in their enrollment period

Thanks in Advance

[PGStats]

Why don't you give the rules explicitly? Relative to your example, the rules seem inconsistent. ABC has a gap of 1 (March) and is still enrolled, why drop him? GHI is no longer enrolled, why keep him?

PG

[Alpay]

Counting number of months between EXPDAT from previous record and EFFDAT for current record for a given MembNo.

If a gap exists in enrollment then gap variable incremented.

data have;

input Membno $ GRPNUM $ EFFDAT anydtdte. EXPDAT anydtdte.;

format EffDat ExpDat date9.;

datalines;

ABC LI 20120201 20120201

ABC H1NAS 20120201 20120331

ABC PAMAS 20120401 99999999

DEF LI 20120101 20120229

DEF H1NAS 20120401 20120430

DEF PAMAS 20120601 99999999

GHI LI 20120201 20120229

GHI H1NAS 20120301 20120331

GHI PAMAS 20120401 20120430

GHI PAMAS 20120601 20120630

JKL LI 20120401 20120430

JKL H1NAS 20120501 20120531

JKL PAMAS 20120601 99999999

MNO LI 20120101 20120331

MNO H1NAS 20120401 20120430

;

run;

data want;

set have;

by membno;

lag_expdat = LAG(ExpDat);

if first.membno then gap = 0;

else gap + (MAX(0,INTCK('month',lag_expdat,effdat)-1));

retain gap;

if last.membno and gap;

keep membno grpnum gap;

run;

[raveena]

Thanks Alpay !!!