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 !
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
Can you describe the rules for setting the expiry_date, and why some records are apparently kept and others removed?
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;
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
Yes, you can put any condition you want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.