BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
someone_new
Fluorite | Level 6

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
FloydNevseta
Pyrite | Level 9

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

View solution in original post

7 REPLIES 7
ballardw
Super User

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

someone_new
Fluorite | Level 6
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
Lapis Lazuli | Level 10

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
Fluorite | Level 6
what if my desc column do not have 'cancel' is there any other method?
FloydNevseta
Pyrite | Level 9

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
Fluorite | Level 6
if the desc column do not have 'cancel' is there any way to code it?
CurtisMackWSIPP
Lapis Lazuli | Level 10

Yes, you can put any condition you want.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2432 views
  • 0 likes
  • 4 in conversation