Here's a way of dealing with consecutive positive values, as well as positive values in the first or last record of the dataset (it will use the first or last negative value in these instances). It requires a full pass of the data to identify the row numbers with positive values, then uses the direct access method (POINT=) to calculate the mean of negative values either side, then finally modifies the original dataset (again using POINT=) to replace the incorrect values. There may be a more efficient method, but try it if you wish. data have; input id $ date :mmddyy10. value; format date mmddyy10.; cards; S12 07/21/2011 -20 S12 07/23/2011 -50 S13 07/15/2011 -40 S13 07/16/2011 60 S13 07/17/2011 -100 S14 07/24/2011 10 S14 07/24/2011 20 S14 07/24/2011 -30 S14 07/24/2011 -20 ; run; /* Identify row numbers with positive values */ data invalid (keep=recno); set have (keep=value); if value>=0 then do; recno=_n_; output; end; run; /* Directly access source data to calculate mean of values either side of positives */ data mean_value; set invalid; do i=1 to nobs until (value1<0 or recno_prev=1); recno_prev=max(1,recno-i); set have (keep=value rename=(value=value1)) point=recno_prev nobs=nobs; end; value1=ifn(value1>=0,.,value1); /* if first value in dataset is positive, set it to missing */ do j=1 to nobs until (value2<0 or recno_next=nobs); recno_next=min(nobs,recno+j); set have (keep=value rename=(value=value2)) point=recno_next nobs=nobs; end; value2=ifn(value2>=0,.,value2); /* if last value in dataset is positive, set it to missing */ mean_value=mean(value1,value2); run; /* Replace missing values with calculated means */ /* Direct access method used */ data have; set mean_value; modify have point=recno; value=mean_value; run;
... View more
