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.
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!
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.
Ready to level-up your skills? Choose your own adventure.