data - filling gaps

[hellorc]

Hello SAS community,

I have quick question related to data structure or formatting. I have the current data that looks like:

data have;
input id city $ week;
datalines;
1 A 3
1 B 6
1 B 7
1 C 10
;
run;

But I would actually need to fill the weeks after the first week the person shows up in the city (i.e.):

data want;
input id city $ week;
datalines;
1 A 3
1 A 4
1 A 5
1 B 6
1 B 7
1 B 8
1 B 9
1 C 10
;
run;

The week gaps would be filled with the former city value until a city change. There are about 1000 subjects in the data.

Might someone be willing to provide assistance how to code it?

Thank you

[yabwon]

Try this:

data have;
input id city $ week;
datalines;
1 A 3
1 B 6
1 B 7
1 C 10
2 A 3
2 B 6
2 B 9
2 C 10
2 C 15
;
run;


data have2;
set have;
by id;
if not first.id;
rename week=week2;
drop city;
run;

data want;
merge have have2; 
by id;
if last.id then 
  output;
else 
  do week = week to week2-1;
    output;
  end;
drop week2;
run;

proc print data = want; 
run;

Bart

[hellorc]

Hello yabwon, thank you so much for your reply. I have an extra question if you don't mind. Let's say the final week is 20 for everyone. But the last.id for subject 1 is week=10. and for subject 2 is week=15 as in your code. How would you adjust the code so that there would be:

1 C 10
1 C 11
...
1 C 20

for subject 1?

[yabwon]

Try this (for 3 it's 23 just to test if it's covering info from data):

data have;
input id city $ week;
datalines;
1 A 3
1 B 6
1 B 7
1 C 10
2 A 3
2 B 6
2 B 9
2 C 10
2 C 15
3 A 3
3 B 6
3 B 9
3 C 23
;
run;


data have2;
set have;
by id;
if not first.id;
rename week=week2;
drop city;
run;

data want;
merge have have2; 
by id;
if last.id then 
  do week = week to max(week2,20);
    output;
  end;
else 
  do week = week to week2-1;
    output;
  end;
drop week2;
run;

proc print data = want; 
run;

Bart

[Ksharp · Accepted solution]

data have;
input id city $ week;
datalines;
1 A 3
1 B 6
1 B 7
1 C 10
2 A 3
2 B 6
2 B 9
2 C 10
2 C 15
;
run;

data want;
merge have have(keep=id week rename=(id=_id week=_week) firstobs=2);
output;
if id=_id then do;
 do week=week+1 to _week-1;
  output;
 end;
end;
drop _:;
run;

[hellorc]

Thank you both, yabwon and Ksharp!! Both worked!