You have several ways to do your job. But you indicate that you are not getting the expected output.
In your sample data set, you have not provided one condition to be checked. You want the TEMP with a missing value for the first and last date of the ZIP be retained as such. So I have modified your input data for checking the condition :
data have;
INFILE CARDS EXPANDTABS;
input date date9. zip temp;
cards;
01Jan2005 90001 .
02Jan2005 90001 51
03Jan2005 90001 52
04Jan2005 90001 49
05Jan2005 90001 48
06Jan2005 90001 .
07Jan2005 90001 50
08Jan2005 90001 51
09Jan2005 90001 52
10Jan2005 90001 49
11Jan2005 90001 54
12Jan2005 90001 60
13Jan2005 90001 50
14Jan2005 90001 .
15Jan2005 90001 52
16Jan2005 90001 49
17Jan2005 90001 .
18Jan2005 90001 60
19Jan2005 90001 50
20Jan2005 90001 .
01Jan2005 90002 60
02Jan2005 90002 51
03Jan2005 90002 52
04Jan2005 90002 52
05Jan2005 90002 52
06Jan2005 90002 .
07Jan2005 90002 .
08Jan2005 90002 .
09Jan2005 90002 .
10Jan2005 90002 .
11Jan2005 90002 .
12Jan2005 90002 .
13Jan2005 90002 .
14Jan2005 90002 .
15Jan2005 90002 52
16Jan2005 90002 49
17Jan2005 90002 52
18Jan2005 90002 49
19Jan2005 90002 48
20Jan2005 90002 .
;
run;
Next, we scan the DATA SET to get the SMALLEST and the BIGGEST Date to SIZE the Array to be used in the following Program. If you know these before hand, you can skip this step but you need to create Macro variables for them (bDate, eDate). The array is sized for the biggest group. So, small groups will also fit into it.
data _null_;
retain eDate 0 sDate 999999999;
set have end = eof;
if date > eDate then eDate = date;
if date < sDate then sDate = date;
if eof then do;
call symputx('sDate', sDate);
call symputx('eDate', eDate);
end;
run;
The program uses two DO-LOOPs. This do-loop is known as DoW-loop. In the first do-loop, TEMP for each DATE is saved in a _temporary_ array. Only one Zip at a time is processed. We save the first DATE and the last DATE to check your boundary conditions. In the second do-loop, the same group is scanned again and checked for your requirements. Here is the program:
options fullstimer;
data want;
array k[&sDate:&eDate] _temporary_;
do until(last.zip);
set have;
by zip;
k[date] = temp;
if first.zip then startD = date;
if last.zip then endD = date;
end;
do until(last.zip);
set have;
by zip;
if (date = startD & temp = .) | (date = endD & temp = .) then temp_new = .;
else if not missing(temp) then temp_new = temp;
else do;
tf = .; td = .;
do m = date+1 to date+7; * going forward;
if k[m] > 0 then do; tf = k[m]; leave; end;
end;
do m = date - 1 to date - 7 by -1; * going backward;
if k[m] > 0 then do; td = k[m]; leave; end;
end;
if missing(tf) or missing(td) then temp_new = .;
else temp_new = (tf + td) * 0.5;
end;
output;
end;
keep date zip temp temp_new;
run;
Here is the OUTPUT generated by this program:
Obs date zip temp temp_new
1 16437 90001 50 .
2 16438 90001 51 51.0
3 16439 90001 52 52.0
4 16440 90001 49 49.0
5 16441 90001 48 48.0
6 16442 90001 . 49.0
7 16443 90001 50 50.0
8 16444 90001 51 51.0
9 16445 90001 52 52.0
10 16446 90001 49 49.0
11 16447 90001 54 54.0
12 16448 90001 60 60.0
13 16449 90001 50 50.0
14 16450 90001 . 51.0
15 16451 90001 52 52.0
16 16452 90001 49 49.0
17 16453 90001 . 54.5
18 16454 90001 60 60.0
19 16455 90001 50 50.0
20 16456 90001 51 .
21 16437 90002 60 .
22 16438 90002 51 51.0
23 16439 90002 52 52.0
24 16440 90002 52 52.0
25 16441 90002 52 52.0
26 16442 90002 . .
27 16443 90002 . .
28 16444 90002 . 52.0
29 16445 90002 . 52.0
30 16446 90002 . 52.0
31 16447 90002 . 52.0
32 16448 90002 . 52.0
33 16449 90002 . .
34 16450 90002 . .
35 16451 90002 52 52.0
36 16452 90002 49 49.0
37 16453 90002 52 52.0
38 16454 90002 49 49.0
39 16455 90002 48 48.0
40 16456 90002 60 .
You can compare all the methods and see how FULLSTIMER gives Time and Memory. Enjoy.
