okay, i will try that. thank you for your help. ... View more

  here is sample data of what i have and want.    data have; input date zip temp; datalines; 01Jan2005 90001 50 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 51 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 60 ; run; data want; input date zip temp temp_new; datalines; 01Jan2005 90001 50 02Jan2005 90001 51 03Jan2005 90001 52 04Jan2005 90001 49 05Jan2005 90001 48 06Jan2005 90001 . 49 07Jan2005 90001 50 08Jan2005 90001 51 09Jan2005 90001 52 10Jan2005 90001 49 11Jan2005 90001 54 12Jan2005 90001 60 13Jan2005 90001 50 14Jan2005 90001 . 51 15Jan2005 90001 52 16Jan2005 90001 49 17Jan2005 90001 . 54.5 18Jan2005 90001 60 19Jan2005 90001 50 20Jan2005 90001 51 01Jan2005 90002 60 02Jan2005 90002 51 03Jan2005 90002 52 04Jan2005 90002 52 05Jan2005 90002 52 06Jan2005 90002 . . 07Jan2005 90002 . . 08Jan2005 90002 . 52 09Jan2005 90002 . 52 10Jan2005 90002 . 52 11Jan2005 90002 . 52 12Jan2005 90002 . 52 13Jan2005 90002 . . 14Jan2005 90002 . . 15Jan2005 90002 52 16Jan2005 90002 49 17Jan2005 90002 52 18Jan2005 90002 49 19Jan2005 90002 48 20Jan2005 90002 60 ; run; ... View more

second attempt. tried this. no errors, but new dataset looks same as original.    513 proc sort data=test; by zipnum; run; NOTE: Input data set is already sorted, no sorting done. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 514 data want (drop=_d); 515 set test (in=firstpass) 516 test (in=secondpass); 517 by zipnum; 518 519 array thist(%sysevalf('01jan2005'd-7):%sysevalf('31dec2010'd+7)) _temporary_; 520 if first.zipnum then call missing(of thist{*}); 521 if firstpass then thist{date}=meantemp; 522 523 if secondpass; 524 if meantemp=. and lag(zipnum)^=zipnum and last.zipnum=0 then do _d=1 to 7 while(meantemp=.); 525 meantemp=mean(thist{date - _d},thist{date + _d}); 526 end; 527 run; NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 4172 at 525:14 NOTE: There were 3615150 observations read from the data set WORK.TEST. NOTE: There were 3615150 observations read from the data set WORK.TEST. NOTE: The data set WORK.WANT has 3615150 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.74 seconds cpu time 0.71 seconds     ... View more

hello,    thank you for this code! i tried the following and am getting an error due to array being out of range. i think it might be because of missing values either at the start or end date. below is the code and error.     445 data WANT; 446 array ZIPS [%sysevalf('01jan2005'd):%sysevalf('31dec2010'd)] _temporary_; 447 array TEMPS[%sysevalf('01jan2005'd):%sysevalf('31dec2010'd)] _temporary_; 448 if _N_=1 then do until(LASTOBS); 449 set test end=LASTOBS; 450 ZIPS [DATE]=zipnum ; 451 TEMPS[DATE]=meantemp; 452 end; 453 set test; 454 by zipnum; 455 if meantemp=. then do; 456 do I=DATE-1 to DATE-7 by -1; 457 if ZIPS[I] ne zipnum then leave; 458 if TEMPS[I] then LAST_TEMP=TEMPS[I]; 459 if LAST_TEMP then leave; 460 end; 461 do I=DATE+1 to DATE+7; 462 if ZIPS[I] ne zipnum then leave; 463 if TEMPS[I] then NEXT_TEMP=TEMPS[I]; 464 if NEXT_TEMP then leave; 465 end; 466 if ^first.zipnum and ^last.zipnum then meantemp=mean(LAST_TEMP,NEXT_TEMP); 467 end; 468 run; ERROR: Array subscript out of range at line 462 column 10. LASTOBS=1 date=31DEC2010 zipnum=90004 meantemp=. FIRST.zipnum=0 LAST.zipnum=1 I=18628 LAST_TEMP=. NEXT_TEMP=. _ERROR_=1 _N_=8764 NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 380 at 466:53 NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 3615150 observations read from the data set WORK.TEST. NOTE: There were 8765 observations read from the data set WORK.TEST. WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 8763 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.19 seconds cpu time 0.15 seconds   ... View more

oh, got it, thanks! it runs without that error now . ... View more

thank you! this seems to fill in averages with nearest before and after, but how can I adjust this to only fill in values if the values it pulls from are within 7 days? thanks! ... View more

thank you! tried the following code, and get the following errors. Ideas? I don't understand the array well enough to figure out how to debug this. looking into it now.    proc sort data=have; by zipnum; run; data want (drop=_d); set have (in=firstpass) have (in=secondpass); by zipnum; array thist{%sysevalf{'01jan2005'd-7}:%sysevalf{'31dec2010'd+7} _temporary_; if first.zipnum then call missing(of thist{*}); if firstpass then thist{date}=meantemp; if secondpass; if meantemp=. and lag(zipnum)^=zip and last.zipum=0 then do _d=1 to 7 while(meantemp=.); meantemp=mean(thist{date - _d},thist{date + _d}); end; run; 181 data test2 (drop=_d); 182 set test (in=firstpass) 183 test (in=secondpass); 184 by zipnum; 185 186 array thist{%sysevalf{'01jan2005'd-7}:%sysevalf{'31dec2010'd+7} _temporary_; - 22 76 ERROR: %SYSEVALF must be followed by an expression enclosed in parentheses. ERROR: %SYSEVALF must be followed by an expression enclosed in parentheses. ERROR 22-322: Syntax error, expecting one of the following: an integer constant, *. ERROR 76-322: Syntax error, statement will be ignored. 187 if first.zipnum then call missing(of thist{*}); 188 if firstpass then thist{date}=meantemp; ERROR: Too many array subscripts specified for array thist. 189 190 if secondpass; 191 if meantemp=. and lag(zipnum)^=zip and last.zipum=0 then do _d=1 to 7 while(meantemp=.); 192 meantemp=mean(thist{date - _d},thist{date + _d}); ERROR: Too many array subscripts specified for array thist. ERROR: Too many array subscripts specified for array thist. 193 end; 194 run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 192:19 192:36 NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.TEST2 may be incomplete. When this step was stopped there were 0 observations and 4 variables. WARNING: Data set WORK.TEST2 was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds ... View more

That worked! Thank you 🙂    ... View more

That is very clear, thank you. I am testing this out with a small sample of my data (and it works perfectly), but, the real dataset is very large. I will allow ample time for running.    Desired intervals at the moment vary because of missing data, but with some more steps I can create a standard interval (where some of the date values are missing instead of being skipped).    If there are quick adjustments to make this process more efficient, suggestions welcome.   Thank you so much! Appreciate it.  ... View more