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 |
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;
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 | 20 | S110 |
2) Why does the inserted lines have an ED value of 20?
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.