Prev: SAS 64-bit windows 7
Next: Check if a macro variable ends with another string
From: Max F on 6 May 2010 00:14
I have a dataset with two dates. The startdate is in chronological
order by id:

Startdate Finishdate id

04NOV07 04JAN08 55
08DEC07 07FEB08 55
02JAN08 03MAR08 55
07MAR08 07MAY08 55
10JUN05 10AUG05 76
17JUN05 16SEP05 76
09FEB06 11APR06 76
05NOV04 06DEC04 79
28MAR07 28MAY07 79



If the Finishdate is greater than the next Startdate, it needs to be
reset to one day before the Startdate. Therefore, the result would
look like this:


Startdate Finishdate id

04NOV07 07DEC07 55
08DEC07 01JAN08 55
02JAN08 03MAR08 55
07MAR08 07MAY08 55
10JUN05 16JUN05 76
17JUN05 16SEP05 76
09FEB06 11APR06 76
05NOV04 06DEC04 79
28MAR07 28MAY07 79

Any help would be much appreciated.
From: Patrick on 6 May 2010 07:08
Resort by descending Startdate, then use lag() function and compare
lag(startdate) with current Finishdate, amend Finishdate if it is
greater than lag(startdate).

HTH
Patrick
From: data _null_; on 6 May 2010 07:40
On May 5, 11:14 pm, Max F <max...(a)yahoo.com> wrote:
> I have a dataset with two dates. The startdate is in chronological
> order by id:
>
> Startdate    Finishdate              id
>
> 04NOV07      04JAN08            55
> 08DEC07      07FEB08            55
> 02JAN08      03MAR08            55
> 07MAR08      07MAY08            55
> 10JUN05      10AUG05            76
> 17JUN05      16SEP05            76
> 09FEB06      11APR06            76
> 05NOV04      06DEC04            79
> 28MAR07      28MAY07            79
>
> If the Finishdate is greater than the next Startdate, it needs to be
> reset to one day before the Startdate. Therefore, the result would
> look like this:
>
> Startdate    Finishdate               id
>
> 04NOV07      07DEC07            55
> 08DEC07      01JAN08            55
> 02JAN08      03MAR08            55
> 07MAR08      07MAY08            55
> 10JUN05      16JUN05            76
> 17JUN05      16SEP05            76
> 09FEB06      11APR06            76
> 05NOV04      06DEC04            79
> 28MAR07      28MAY07            79
>
> Any help would be much appreciated.

A look ahead "merge" can be programmed with two sets.

data test;
input (Startdate Finishdate)(:date.) id;
format S: F: date9.;
cards;
04NOV07 04JAN08 55
08DEC07 07FEB08 55
02JAN08 03MAR08 55
07MAR08 07MAY08 55
10JUN05 10AUG05 76
17JUN05 16SEP05 76
09FEB06 11APR06 76
05NOV04 06DEC04 79
28MAR07 28MAY07 79
;;;;
run;
data test;
set test end=eof;
by id;
if not eof then set test(firstobs=2 keep=F:
rename=(FinishDate=NextFD));
if last.id then call missing(nextfd);
run;
proc print;
run;
From: Max F on 10 May 2010 14:07
On May 6, 4:40 am, "data _null_;" <datan...(a)gmail.com> wrote:
> On May 5, 11:14 pm, Max F <max...(a)yahoo.com> wrote:
>
>
>
>
>
> > I have a dataset with two dates. The startdate is in chronological
> > order by id:
>
> > Startdate    Finishdate              id
>
> > 04NOV07      04JAN08            55
> > 08DEC07      07FEB08            55
> > 02JAN08      03MAR08            55
> > 07MAR08      07MAY08            55
> > 10JUN05      10AUG05            76
> > 17JUN05      16SEP05            76
> > 09FEB06      11APR06            76
> > 05NOV04      06DEC04            79
> > 28MAR07      28MAY07            79
>
> > If the Finishdate is greater than the next Startdate, it needs to be
> > reset to one day before the Startdate. Therefore, the result would
> > look like this:
>
> > Startdate    Finishdate               id
>
> > 04NOV07      07DEC07            55
> > 08DEC07      01JAN08            55
> > 02JAN08      03MAR08            55
> > 07MAR08      07MAY08            55
> > 10JUN05      16JUN05            76
> > 17JUN05      16SEP05            76
> > 09FEB06      11APR06            76
> > 05NOV04      06DEC04            79
> > 28MAR07      28MAY07            79
>
> > Any help would be much appreciated.
>
> A look ahead "merge" can be programmed with two sets.
>
> data test;
>    input (Startdate Finishdate)(:date.) id;
>    format S: F: date9.;
>    cards;
> 04NOV07      04JAN08            55
> 08DEC07      07FEB08            55
> 02JAN08      03MAR08            55
> 07MAR08      07MAY08            55
> 10JUN05      10AUG05            76
> 17JUN05      16SEP05            76
> 09FEB06      11APR06            76
> 05NOV04      06DEC04            79
> 28MAR07      28MAY07            79
> ;;;;
>    run;
> data test;
>    set test end=eof;
>    by id;
>    if not eof then set test(firstobs=2 keep=F:
> rename=(FinishDate=NextFD));
>    if last.id then call missing(nextfd);
>    run;
> proc print;
>    run;- Hide quoted text -
>
> - Show quoted text -

The provided solution does not achieve what I intend to do. Anybody
who can help, please.
From: PJ on 10 May 2010 14:52
Please try below,

data have;

input @1 startdate date. @14 Finishdate date. id $ ;
format startdate finishdate date.;
cards ;
04NOV07 04JAN08 55
08DEC07 07FEB08 55
02JAN08 03MAR08 55
07MAR08 07MAY08 55
10JUN05 10AUG05 76
17JUN05 16SEP05 76
09FEB06 11APR06 76
05NOV04 06DEC04 79
28MAR07 28MAY07 79
;

run;


data want;
merge have have(firstobs = 2 rename =startdate = next_start
keep=startdate);
run;

data want(drop=next_start);
set want;
by id startdate notsorted;
if ^last.id and finishdate > next_start then finishdate =
next_start-1;
put (_all_) (=);
run;

startdate=04NOV07 Finishdate=07DEC07 id=55 next_start=08DEC07
startdate=08DEC07 Finishdate=01JAN08 id=55 next_start=02JAN08
startdate=02JAN08 Finishdate=03MAR08 id=55 next_start=07MAR08
startdate=07MAR08 Finishdate=07MAY08 id=55 next_start=10JUN05
startdate=10JUN05 Finishdate=16JUN05 id=76 next_start=17JUN05
startdate=17JUN05 Finishdate=16SEP05 id=76 next_start=09FEB06
startdate=09FEB06 Finishdate=11APR06 id=76 next_start=05NOV04
startdate=05NOV04 Finishdate=06DEC04 id=79 next_start=28MAR07
startdate=28MAR07 Finishdate=28MAY07 id=79 next_start=.
 |  Next  |  Last
Pages: 1 2
Prev: SAS 64-bit windows 7
Next: Check if a macro variable ends with another string