Remove 'duplicates' from membership data where term date differs

[rrenzi]

Using SAS enterprise guide 7.1  32 bit.

My department receives a monthly membership listing.  January's data might not show a term date for a member, but February will.  People will often change subscriptions, so a member can have multiple effective and expired dates in a year on various products.  Below is the bare minimum data. EFFDAT is Effective date.  EXPDAT is a term/expired date.  279999 means there is no term date.  All subscriptions begin on the first of the month and end the last day of the month.  So 201804 means an effective date of 4/1/2018.   For 201804 initially there was no term date, but then they termed 201905 (or 5/30/2015) and went to a new product.  They started a new product 201906.  Initially there was no term date on 201906 effective date but then they termed 201910 and started a new product 201911 with no term date.    I have a dozen fields I want to append to the records when I get to the desired result.  Below is one sample member record with the desired result below.  I do not want to change dates to a date format.  I already have a methodology to count months of membership using the date formats used. I am a novice and write basic queries.  I think this will need some elementary code.  No idea where that goes in my query, but I can probably ask a coworker.  I have summarized my data to the data below, but I need another level of summary as shown in desired results.  Thanks- I hope this is easy.

Data
MEMBNO	EFFDAT	EXPDAT
23000	201804	279999
23000	201804	201905
23000	201906	279999
23000	201906	201910
23000	201911	279999
Desired result
MEMBNO	EFFDAT	EXPDAT
23000	201804	201905
23000	201906	201910
23000	201911	279999

[Shmuel]

proc sort data=have by membno effdat descending  expdat; run;

data want;
 set have;
  by membno effdat;
      if last.effdat;
run;

if you data is already sorted as required you can ignore the sort step.

[rrenzi]

thank you.  I've written very few SQL statements.  This is what I have

PROC SQL;
CREATE TABLE work.MEMBERSHIP_PULL_2019 (compress=yes) AS
SELECT
t1.Membno,
t1.Effdat,
t1.Expdat,

FROM WORK.tbl_2019_MEMS_Start_end

data work.Membership_Final_2019;
set tbl_2019_MEMS_Start_end;
by membno effdat;
if last.effdat;

SAS has the words set,by and if in red on the last three lines.  It doesn't like them.... 😞  Any additional help would be appreciated.

Thank you

[ballardw]

Generally the first thing when working with dates is to have date values.

Which  may make answers to the next question easier. What do you do when there is a gap between an EXPDAT and EFFDAT such as Expdat=201905 and then next Effdat is 201909 for instance for the same member?

And by "query" do you mean Proc Sql? That is often not the best tool where handling data requires a specific sequence.

And I would be very interested in seeing  how you get your data to sort with that Expdat value for 'missing'.

[rrenzi]

I assumed I would need a SQL statement.   I can not change the dates to a date value.  It would create more problems.  There are no null values for end date.  The dummy value is always in place until an actual end date in in place.  I just need to get the data summarized as is.  I'm pretty new with SAS- two months.  So I might not be answering questions well.

[ballardw]

---

@rrenzi wrote:

I assumed I would need a SQL statement.   I can not change the dates to a date value.  It would create more problems.  There are no null values for end date.  The dummy value is always in place until an actual end date in in place.  I just need to get the data summarized as is.  I'm pretty new with SAS- two months.  So I might not be answering questions well.

---

I suspect in the long run you'll run into a number of issues with not using dates, such as any graphing like "number of members with Expdat", but leaving that aside, you have not answered about GAPS of values. That is not a the same as NULL.

You may need to show what happens if the next Effdat is not exactly one month after the last non-missing Expdat. I have a very hard time believing that your members never have a lapse, forget to pay a bill, or some such. So, you need to show what your data looks like when that happens, and then show the expected resolution.

[rrenzi]

Gaps do not occur.  We are perfect.  🙂  I work in a highly regulated industry.  In the highly unlikely event a person leaves for a month they are given a new member number on return.   More than you want to know, we can not terminate a member.  A member terminates when their sponsor terminates the contract.  The member may return typically a year or two in the future, but they will have a new member number upon return.

[Patrick · Accepted solution]

From how you describe your data below should work (also in case the date variables are of type numeric).

Not storing/converting the values of your date variable to SAS Date values as early as possible is not good practice and will make your coding downstream just harder, less robust and harder to understand and maintain.

data have;
  input (MEMBNO EFFDAT EXPDAT) ($);
  datalines;
23000 201804 279999
23000 201804 201905
23000 201906 279999
23000 201906 201910
23000 201911 279999
;

proc sql;
/*  create table want as*/
  select *
  from have
  group by membno, effdat
  having min(EXPDAT)=EXPDAT
  ;
quit;

[rrenzi]

I am very new.  Thank you for the solution.  I used a Min(EXPDAT) expression in the expression builder and it worked.  Thank you very much!