topic Re: auto fill in SAS Procedures

auto fill

Mit — Tue, 21 Feb 2012 03:03:15 GMT

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

Re: auto fill

Tom — Tue, 21 Feb 2012 05:09:55 GMT

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;

Re: auto fill

shivas — Tue, 21 Feb 2012 06:04:45 GMT

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 ;

Re: auto fill

Mit — Tue, 21 Feb 2012 23:51:44 GMT

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

Re: auto fill

Mit — Tue, 21 Feb 2012 23:53:07 GMT

Hi Shivas

Thanks for that. This has worked.

Mit