Fill missing values with proc expand

[art297]

DN: Have never run it before, but will now.

Thanks again!

[art297]

DN: With the debugger I can see what is happening, but still don't fully understand the logic.  I only used the watch and go commands.  Should I be including others?

For anyone else, like me (who has never played with the debugger), a step-by-step explanation can be found at:

http://www2.sas.com/proceedings/sugi25/25/btu/25p052.pdf

[data_null__]

It may be that watching with the debugger may not let you see within the UPDATE statement.

I think the key piece of logic is that when an OBS is read from the transaction data any variable that is missing "RETAINs" its value from the last iteration of the data step loop.  Observations with no missing variables pass throught unchanged and become the next LOCF value if the next observation has variables(s) with missing values.

[art297]

DN: Much appreciated!  Is the approach documented anywhere or was this just another of your many insights?

[data_null__]

Me insight are you joking.

I'm sure I must have seen it before but I can't remember.

[Haikuo]

With DN's approach, you should be fine.  If you still want to play with the approach initiated by Art, maybe you need to define the new variable as character early in your code:

data want (drop=Monthly_Income

           rename=(Locf=Monthly_Income));

Length locf $20;

....................

Locf = coalesceC(Monthly_Income,locf);

Try it and let us know if it works.

Haikuo

[data_null__]

This works with character or numeric data.  It carries all missing variables forward.

data student;
   do student=1,2;
      do month=1 to 10;
         input CVAR $ @;
         output;
         end;
      end;
   stop;
   cards;
100 . 110 110 110 . 110 120 120 120
100 .   . 110 110 . 110 120 .   120
;;;;
   run;
data studentLOCF;
   update student(obs=0) student;
   by student;
   output;
   run;

[Ksharp]

I think the code can be shorter.

data student;
   do student=1,2;
      do month=1 to 10;
         input CVAR $ @;
         output;
         end;
      end;
   stop;
   cards;
100 . 110 110 110 . 110 120 120 120
100 .   . 110 110 . 110 120 .   120
;;;;
   run;
data studentLOCF(drop=_:);
   set student(rename=(cvar=_cvar));
   retain cvar ;
   if not missing(_cvar) or student ne lag(student) then cvar=_cvar;
   run;

Ksharp

[data_null__]

It would be difficult to make the code shorter and you have not accomplished that, just the opposite in fact.  Your approach will need an IF statement for each variable that needs to be LOCFed.

[art297]

DN: Me thinks that KSharp's method will run slightly faster, regardless of the number of variables involved, but that slight saving will be immediately lost given the extra coding time involved and the increased risk of introducing error with all of that extra coding.

[art297]

I had tested it before posting my response.  I tested it on 1,2 3,4,5 and 6 variables, with 1,000,000 records.