2-in-1 program

[sasprogram2017]

Hello everyone, I couldn't come up with a program that adjust dates (in duplicates: use start date from 1st record and use stop date from 2nd record) and remove duplicate records.

data test;
input id $ startdate yyyymmdd. stopdate yyyymmdd. ;
datalines;
101 20150304 20190302
101 20190302 20160622
102 20150916 20190914
102 20190914 20161211
103 20060530 20070530
104 20070413 20071113
;

Output needed:

101 20150304 20160622
102 20150916 20161211
103 20060530 20070530
104 20070413 20071113

I'm tired of thinking and thanks a ton for solving my problem...

[novinosrin]

Hi @sasprogram2017 are your records always sets of 2 or 1 for every id?

Welcome to SAS forum

[sasprogram2017]

Thanks Novinosrin.

No, not always sets of 2 for every ID, only when the start date > stop date. So I'm trying to combine the duplicates by taking the start date from first record and stop date from the duplicate record.

Thanks again

[novinosrin · Accepted solution]

Sorry for the delay, does this help?

data test;
input id $ startdate : yymmdd8.  stopdate : yymmdd8. ;
format startdate stopdate yymmdd10.;
datalines;
101 20150304 20190302
101 20190302 20160622
102 20150916 20190914
102 20190914 20161211
103 20060530 20070530
104 20070413 20071113
;

data want;
set test;
by id;
startdate=ifn( last.id and startdate>stopdate,lag(startdate),startdate);
if last.id ;
run;

[ChrisNZ]

1. Always test the programs you provide to us. Your doesn't run as is.

2. This works (but @novinosrin's solution is more efficient):

data WANT;
  merge HAVE 
        HAVE(firstobs=2 rename=(ID=NEXT_ID STARTDATE=NEXT_STARTDATE STOPDATE=NEXT_STOPDATE));
  if ID = lag(ID) then return;
  if ID=NEXT_ID then STOPDATE=NEXT_STOPDATE; 
  output; 
run;

ID	STARTDATE	STOPDATE
101	20150304	20160622
102	20150916	20161211
103	20060530	20070530
104	20070413	20071113

[novinosrin]

Guru @ChrisNZ Thank you for the kind words. I wanted to write to you PM but let me take this opportunity to ask. Why isn't your book on safari online subscription. Of course I have a paid subscription. Other SAS gurus have got it there. Can you consider?

@Everybody my Kind apologies for deviation of the thread but i needed to clear my urge. 

[ChrisNZ]

@novinosrin Thank you!

The third edition (200+ pages) is only a few weeks away, and this is a very relevant question as I am pondering selling digital copies.

I like paper and I want my technical books on a shelf in front of me, but I use and understand the appeal of a digital reader.

SAS Publishing uses Safari, so their books are there.

Since my book is not published by SAS (long story, not too sure what went wrong), it is not there automatically.

I must admit never considering Safari online, and this raises even more questions about enabling digital copies.

I am not a fan of subscriptions myself, but as for paper, I see that the world seems to be moving this way.

We can continue this discussion offline and I'll quiz you. 🙂

[sasprogram2017]

Thanks so much Novinosrin and Chrisnz, this is exactly what I needed.