I have been struggling with this. I have sequential data that is missing some rows...and I need to fill them in.
I have:
ColA ColB ColC Diff
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
I want:
ColA ColB ColC Diff
A B C 1
A B C 2
A B C 3
A B C 4
D E F 1
D E F 2
D E F 3
D E F 4
See this:
data have;
input ColA $ ColB $ ColC $ Diff;
cards;
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
;
run;
data want (drop=ldif);
set have;
by cola colb colc;
ldif = lag(diff);
if not first.colc then do diff = ldif + 1 to diff;
output;
end;
else output;
run;
It's tough to generalize from your example.
Do you always want 4 rows? Or do you want the number of rows to be flexible based on the input data?
Flexible ..the length could go on for a while. I wanted to show a simple example. I have many missing rows and need to fill them in with the information from the previous row.
See this:
data have;
input ColA $ ColB $ ColC $ Diff;
cards;
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
;
run;
data want (drop=ldif);
set have;
by cola colb colc;
ldif = lag(diff);
if not first.colc then do diff = ldif + 1 to diff;
output;
end;
else output;
run;
I think this is what you are asking for:
data want;
set have;
prior_a = lag(a);
prior_b = lag(b);
prior_c = lag(c);
skip = dif(diff);
if skip <= 1 then output;
else do;
temp_a = a;
temp_b = b;
temp_c = c;
a = prior_a;
b = prior_b;
c = prior_c;
do diff = (diff - skip + 1) to (diff - 1);
output;
end;
a = temp_a;
b = temp_b;
c = temp_c;
output;
end;
drop prior_: temp_: skip;
run;
It's untested, so if it needs a little tweaking just ask.
@Jarious What about the possibility of :
I have:
ColA ColB ColC Diff
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
T U V 1 /*is this possible ?*/
T U V 5/*is this possible ?*/
data have;
input ColA $ ColB $ ColC $ Diff;
cards;
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
T U V 1
T U V 5
;
run;
proc sort data=have out=_have ;
by ColA ColB ColC descending diff;
run;
data want;
set _have;
by ColA ColB ColC;
if first.ColA and first.Colb and first.Colc then do diff=1 to diff;
output;
end;
else return;
run;
data have;
input ColA $ ColB $ ColC $ Diff;
cards;
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
;
run;
data want;
merge have have(rename=(cola=_cola colb=_colb colc=_colc diff=_diff) firstobs=2);
output;
if cola=_cola and colb=_colb and colc=_colc then do;
do i=diff+1 to _diff-1;
diff=i;output;
end;
end;
drop _: i;
run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.