topic Re: Filling up missing values in SAS Programming

Filling up missing values

Khaladdin — Wed, 11 Jan 2023 16:33:59 GMT

I have the following dataset:

Group Value

A .

A 1

A .

B .

B 1

B .

For each group, I want to fill the missing values of the previous two rows with the non-missing values. Specifically, I want to get the following data:

Group Value

A .

A 1

A .

B 1

B .

I hope I could explain myself 🙂

Many thanks in advance!

Best,

Khaladdin

Re: Filling up missing values

ErikLund_Jensen — Wed, 11 Jan 2023 16:55:27 GMT

Hi @Khaladdin

I wonder what yout purpose is, but the following code will solve this particular case, which looks like a test vase to me..

It demonstrates hoiw to perform look-ahead with extra set statements, and I think it could easily be modified, so it could be used in a real-life situation.

data have;
input Group$ Value;
datalines;
A .
A . 
A .
A 1
A .
B . 
B . 
B 1
B .
B .
;
run;

data want (drop=group2 value2 group3 value3); 
  set have nobs=n;
  if _N_ < n-1 then set have (firstobs=2 rename=(group=group2 value=value2));
  if _N_ < n-2 then set have (firstobs=3 rename=(group=group3 value=value3));
  if (group2 = group and value2 ne .) or (group3 = group and value3 ne .) and value = . then value = max(value2,value3);
run;

Re: Filling up missing values

s_lassen — Wed, 11 Jan 2023 17:09:16 GMT

Something like this, I think:

data want;
  set have;
  by group;
  if first.group then
    _firstobs=_N_;
  if missing(value) then do _point=_N_-1 by -1 to max(_firstobs,_N_-2);
    set have(keep=value) point=_point;
    if not missing(value) then leave;
    end;
  retain _:;
  drop _:;
run;

(not tested, as you did not supply test data in data step format)

Re: Filling up missing values

Khaladdin — Wed, 11 Jan 2023 21:30:14 GMT

Thank you very much! It works too. However, the next code works more efficiently in terms of timing.

Re: Filling up missing values

Khaladdin — Wed, 11 Jan 2023 21:30:28 GMT

Thanks!

Re: Filling up missing values

Ksharp — Thu, 12 Jan 2023 11:36:33 GMT

data have;
input Group$ Value;
datalines;
A .
A . 
A .
A 1
A .
B . 
B . 
B 1
B .
B .
;
run;

data want;
merge have
      have(keep=Group Value rename=(Group=Group1 Value=Value1 ) firstobs=2)
   have(keep=Group Value rename=(Group=Group2 Value=Value2 ) firstobs=3);
Value=coalesce( Value, ifn(Group=Group1,Value1,.),ifn(Group=Group2,Value2,.) );
drop Group1 Value1 Group2 Value2;
run;