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
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;
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
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
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;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.