Assign 999 to subsequent blank rows

[bharath86]

Hi @Kurt_Bremser 

The below code doesnt consider subsequent blank values if there are two or more blank rows in between.

This only works on first blank row. Sorry if wasnt that clear yesterday. Can you please check this for me.  

data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b  
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c  
2 4 d 2
2 5 e 2
2 6 f  
2 7 g  
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;
run;



data want;
set have;
by id;
set /* create a "look-ahead" */
  have (
    firstobs=2
    keep=id value
    rename=(id=_id value=_value)
  )
  have ( /* this is needed to prevent a premature end of the data step */
    obs=1
    keep=id value
    rename=(id=_id value=_value)
  )
;
_lvalue = lag(value);
if
  not (first.id or last.id) and
  value = . and _lvalue ne . and _value ne .
then desired = 999;
else desired = value;
drop _:;
run;

[Kurt_Bremser]

I changed my earlier code to SQL, as multiple comparisons over an arbitrary number of observations can be done there:

data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b  
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c  
2 4 d 2
2 5 e 2
2 6 f  
2 7 g  
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;

proc sql;
create table want as
  select
    t1.*,
    max(case
      when t1.value ne .
      then t1.value
      else case
        when t2.value ne . and t3.value ne .
        then 999
        else .
      end
    end) as desired
  from have t1 left join have t2
  on t1.id = t2.id and t1.sequence > t2.sequence
  left join have t3
  on t1.id = t3.id and t1.sequence < t3.sequence
  group by t1.id, t1.sequence, t1.section, t1.value
;
quit;

[Ksharp · Accepted solution]

How about this one .

data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b  
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c  
2 4 d 2
2 5 e 2
2 6 f  
2 7 g  
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;
run;
data want;
 do until(last.value);
  set have;
  by id value notsorted;
  if first.id then first=1;
  if last.id then last=1;
 end;

  do until(last.value);
  set have;
  by id value notsorted;
  desired=value;
  if not first and not last and missing(value) then desired=999;
  output;
 end;
 drop first last;
 run;