auto fill

[Mit]

Hi

I would like to replace the blank cells with the last variable value until a new variable value is found.

I have tried using lag function but the number of blanks are not fixed. The number of consecutive blank cells varies.

Attached is the sample data in csv. I would like to get 'data after' from 'data before'.

Any help will me much appreciated.

Thanks

Mit

[Tom]

What code did you try?  The normal problem users have with LAG() is to think that it means to take the value from the previous observation.  Instead it functions like a stack and so if you execute it conditionally the behaviour is not at all to retreive the value from the previous observation.

data want ;

   set have;

   lag_online=lag(online);

   lag_radio=lag(radio);

   lag_sem =lag(sem);

   lag_mtv=lag(mtv);

   if online=. then online=lag_online;

   if radio=. then radio=lag_radio;

   if sem=. then sem=lag_sem;

   if mtv=. then mtv=lag_mtv;

   drop lag_: ;

run;

[Mit]

Hi Tom

Thanks for that. However I used the same code and it repeats the last value once only. There are lag(1) and lag(2) as well. The later will repeat the value twice.

In my data there is not fixed number of times the value would be repeated.

Mit

[shivas · Accepted solution]

Hi,

Try this ...

data samp  ;

   set before ; retain online1 radio1 sem1;

   if online = . then online =online1 ;

   else online1 = online ;

   if radio = . then radio =radio1 ;

   else radio1 = radio ;

   if sem = . then sem =sem1 ;

   else sem1 = sem ;

drop online1 radio1 sem1;

run ;

[Mit]

Hi Shivas

Thanks for that. This has worked.

Mit