Revised Expiry Date

[someone_new]

Hi not sure anyone know how to code this in sas ?

basically the situation is i have 2 club members, where they upgrade their level as shown below. What i need to capture their "adjusted enddate", which equvalent to my expiry_version

From this table:

ID             startdate             enddate             desciption                               effective date          

101          1 Jan 2010         1 Jan 2011         level 1                                    1 Jan 2010                  

101          1 Jan 2010         1 Jan 2011         cancel level 1                         1 Jul 2010                   

101          1 Jan 2010         1 Jan 2011         level 2                                    1 Jul 2010                   

102          1 Jul 2010          1 Jul 2011          level 2                                     1 Jul 2010                

102          1 Jul 2010          1 Jul 2011          cancel level 2                         1 Aug 2010

To this table:

ID             startdate             enddate             effective date        expiry_version             Description

101          1 Jan 2010         1 Jan 2011         1 Jan 2010            1 Jul 2010                      level 1

101          1 Jan 2010         1 Jan 2011         1 Jul 2010             1 Jan 2011                      level 2

102          1 Jul 2010          1 Jul 2011           1 Jul 2010            1 Aug 2010                     level 2

i found it abit hard to code modify the table since i have minimal experience in SAS.

thanks !

[ballardw]

Can you describe the rules for setting the expiry_date, and why some records are apparently kept and others removed?

[someone_new]

For example, assume the membership in force for 1 year. ID 101. The member join 1 jan 2010 as level 1 then the start date for this member is 1 jan 2010 and theoritically end at 1 jan 2011.

On 1 jul 2010, ID 101 upgrade to level 2 so for level 1 it effective end at 1 Jul 2010 and continue the remaining period in level 2 effective from 1 Jul 2010 until 1 Jan 2011.

So basically, the resulting table for each level, it has the "revised expiry" date. In this example, after the revision, level 1 start on 1 jan 2010 and end at 1 jul 2010 while level 2 start on 1 Jul 2010 and end at 1 jan 2011

Thanks!

[CurtisMackWSIPP]

For problems like this I usually sort the dataset by ID and effective date, and then use a combination of first.id, last.id, and if statements to set values and control output.  Something like this: (this is pseudo code)

proc sort data = mydata;

  by ID effective_date;

run;

data results;

  set mydata;

  by ID effective_date;

  if first.id then do;

    newstartdate = startdate;

    newenddate = enddate;

  end;

  if index(description,"cancel") then do;

    newenddate = effective_date;

    output;

  end;

  else if last.id then output;

run;     

[someone_new]

what if my desc column do not have 'cancel' is there any other method?

[FloydNevseta · Accepted solution]

Here's your data as presented.

data have;
input id startdate :date9. enddate :date9. _desc &:$15. _effdate :date9.;
format startdate enddate _effdate date9.;
datalines;
101    1Jan2010      1Jan2011     level 1             1Jan2010
101    1Jan2010      1Jan2011     cancel level 1      1Jul2010
101    1Jan2010      1Jan2011     level 2             1Jul2010
102    1Jul2010      1Jul2011     level 2             1Jul2010
102    1Jul2010      1Jul2011     cancel level 2      1Aug2010
;
run;

This produces your desired output. Using a DOW loop will automatically retain values unless explicitly set.

data want (drop=_:);
do until (last.id);
  set have;
  by id;
  if _desc ne: 'cancel' then do;
    effective_date = _effdate;
    expiry_version = enddate;
    description = _desc;
  end;
  else do;
    expiry_version = _effdate;
  end;
if last.id or _desc =: 'cancel' then output;
end;
format effective_date expiry_version date9.;
run;

id startdate enddate effective_date expiry_version description
101 01JAN2010 01JAN2011 01JAN2010 01JUL2010 level 1
101 01JAN2010 01JAN2011 01JUL2010 01JAN2011 level 2
102 01JUL2010 01JUL2011 01JUL2010 01AUG2010 level 2

[someone_new]

if the desc column do not have 'cancel' is there any way to code it?

[CurtisMackWSIPP]

Yes, you can put any condition you want.