Help using Base SAS procedures

auto fill

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 83
Accepted Solution

auto fill

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

Attachment
Attachment

Accepted Solutions
Solution
‎02-21-2012 01:04 AM
Super Contributor
Posts: 349

Re: auto fill

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 ;

View solution in original post


All Replies
Super User
Super User
Posts: 6,494

Re: auto fill

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;

Frequent Contributor
Frequent Contributor
Posts: 83

Re: auto fill

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

Solution
‎02-21-2012 01:04 AM
Super Contributor
Posts: 349

Re: auto fill

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 ;

Frequent Contributor
Frequent Contributor
Posts: 83

Re: auto fill

Hi Shivas

Thanks for that. This has worked.

Mit

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1165 views
  • 0 likes
  • 3 in conversation