topic Re: Revised Expiry Date in SAS Programming

Revised Expiry Date

someone_new — Thu, 03 Sep 2020 15:13:26 GMT

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 !

Re: Revised Expiry Date

ballardw — Thu, 03 Sep 2020 15:46:33 GMT

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

Re: Revised Expiry Date

someone_new — Thu, 03 Sep 2020 16:03:50 GMT

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!

Re: Revised Expiry Date

CurtisMackWSIPP — Thu, 03 Sep 2020 16:11:37 GMT

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;

Re: Revised Expiry Date

FloydNevseta — Tue, 08 Sep 2020 12:39:44 GMT

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

Re: Revised Expiry Date

someone_new — Fri, 04 Sep 2020 09:39:04 GMT

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

Re: Revised Expiry Date

someone_new — Fri, 04 Sep 2020 09:39:45 GMT

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

Re: Revised Expiry Date

CurtisMackWSIPP — Wed, 16 Sep 2020 14:23:06 GMT

Yes, you can put any condition you want.