topic Re: how to insert continous dates if there is any gap in SAS Programming

how to insert continous dates if there is any gap

vraj1 — Mon, 26 Aug 2019 09:36:34 GMT

Hi,

I want to insert start and end dates if there is any gap between end date from previous row to start date in the next row.

data WORK.IND;
infile datalines dsd truncover;
input START:DATE9. END:DATE9. UID:$200. ED:32.;
datalines4;
30MAR2016,31MAR2016,S110,5
01APR2016,02APR2016,S110,10
03APR2016,04APR2016,S110,15
05APR2016,17APR2016,S110,20
18APR2016,18APR2016,S110,0
25APR2016,25APR2016,S110,0
18MAY2016,18MAY2016,S110,0
23MAY2016,24MAY2016,S110,0
;;;;

need then dates to be split like something below

30-mar-16	31-mar-16	5	S110
01-apr-16	02-apr-16	10	S110
03-apr-16	04-apr-16	15	S110
05-apr-16	17-apr-16	20	S110
18-apr-16	18-apr-16	0	S110
19-apr-16	24-apr-16	20	S110
25-apr-16	25-apr-16	0	S110
26-apr-16	17-apr-16	20	S110
18-may-2016	18-may-2016	0	S110
19-may-2016	22-may-2016	20	S110
23MAY2016	24MAY2016	0	S110

Re: how to insert continous dates if there is any gap

PeterClemmensen — Mon, 26 Aug 2019 09:42:31 GMT

2 things:

1) I assume that the value 17-apr-16 in the line below should be may, correct?

26-apr-16

17-apr-16

S110

2) Why does the inserted lines have an ED value of 20?

Re: how to insert continous dates if there is any gap

Jagadishkatam — Mon, 26 Aug 2019 10:23:32 GMT

Please try the below code

data WORK.IND;
input START :DATE9. END :DATE9. UID:$200. ED:32.;
format start end date9.;
datalines4;
30MAR2016 31MAR2016 S110 5
01APR2016 02APR2016 S110 10
03APR2016 04APR2016 S110 15
05APR2016 17APR2016 S110 20
18APR2016 18APR2016 S110 0
25APR2016 25APR2016 S110 0
18MAY2016 18MAY2016 S110 0
23MAY2016 24MAY2016 S110 0
;;;;

data want;
set ind;
if _n_ ne 1 and lag(end)+1 ne start then flag=1;
starts = lag(end)+1;
ends=start-1;
output;
start=starts;
end=ends;
format starts ends date9.;
if flag=1 then do;
ED=20;
output;
end;
drop starts ends flag;
run;

proc sort data=want;
by start end;
run;

Re: how to insert continous dates if there is any gap

PeterClemmensen — Mon, 26 Aug 2019 10:20:27 GMT

If so then do

data WORK.IND;
infile datalines dsd truncover;
input START :DATE9. END :DATE9. UID:$200. ED:32.;
format START END DATE9.;
datalines4;
30MAR2016,31MAR2016,S110,5
01APR2016,02APR2016,S110,10
03APR2016,04APR2016,S110,15
05APR2016,17APR2016,S110,20
18APR2016,18APR2016,S110,0
25APR2016,25APR2016,S110,0
18MAY2016,18MAY2016,S110,0
23MAY2016,24MAY2016,S110,0
;;;;

data want(drop=_:);
    merge ind ind(firstobs=2 keep=START END rename=(START=_START END=_END));
    if _START-END gt 1 then do;
        output;
        ED=20;
        START=END+1;
        END=_START-1;
        output;return;
    end;
    output;
run;