topic Re: Fill missing values with proc expand in SAS Procedures

Fill missing values with proc expand

warnerj — Thu, 27 Oct 2011 17:03:03 GMT

I have a dataset with 37K student monthly obs over 10 months. However, not all the obs were taken for each student so I have random missing values scattered throughout my data. I've been trying to use the proc expand procedure to fill the missing valeus with the previous observed value without success. SAS does not say that my code is incorrect but the putput dataset still has the missing values. I've tried it a few ways below and it hasn't worked.

proc expand data=expand out=expand1 method=step;

convert income max_credit_loss_prob/ observed=beginning;

id trans_ym;

by student;

run;

proc expand data=expand out=expand1;

convert income max_credit_loss_prob/ method=step;

id trans_ym;

by student;

run;

What am I doing wrong?

John

Fill missing values with proc expand

art297 — Thu, 27 Oct 2011 17:22:18 GMT

John, Can you provide some sample data and the file you want to achieve based on that data?

I'm not sure, from your problem statement, if you need to create missing records or only need to fill in missing values.

Fill missing values with proc expand

warnerj — Thu, 27 Oct 2011 17:30:10 GMT

I already have a balanced panel with "." in all columns for missing values. I am trying to convert all the observations currently filled with "." to the previous non missing observation. I hope this helps clarify my question.

Example sample data:

Student_ID Month Monthly_Income

1 1 100

1 2 .

1 3 110

1 4 110

1 5 110

1 6 .

1 7 110

1 8 120

1 9 120

1 10 120

What I am trying to get to:

Student_ID Month Monthly_Income

1 1 100

1 2 100

1 3 110

1 4 110

1 5 110

1 6 110

1 7 110

1 8 120

1 9 120

1 10 120

I feel like it should be so simple using proc expand and yet I jsut can't get it to work. Thanks in advance for your help!

John

Fill missing values with proc expand

art297 — Thu, 27 Oct 2011 17:43:09 GMT

Wouldn't it be even easier, then, just doing it in a datastep. Unless I misunderstand what you are trying to do, doesn't the following accomplish the task?:

data want (drop=Monthly_Income

rename=(Locf=Monthly_Income));

do until (last.Student_ID);

set have;

by Student_ID;

Locf = coalesce(Monthly_Income,locf);

output;

end;

run;

Fill missing values with proc expand

warnerj — Thu, 27 Oct 2011 17:59:03 GMT

It looks like it works. I didn't know the locf syntax so that's something very handy to know. The reason(besides the fact I didn't know locf) I was using PROC EXPAND was the ability to specify multiple variables to do it over using the "convert" statement. In your code it looks like I would just use multiple coalesce statements.

The above sample data was missing several variables that are in the real datastep that I also need to do this for. Thank you so much. One last question about your code. Since it doesn't work on the missing value if it is the first in the series ie first.monthly_income what should I do?

Fill missing values with proc expand

art297 — Thu, 27 Oct 2011 18:13:19 GMT

What value would you want to assign in such a case? The first non-missing value for that student?

Fill missing values with proc expand

warnerj — Thu, 27 Oct 2011 18:16:45 GMT

Yes please!

Fill missing values with proc expand

art297 — Thu, 27 Oct 2011 18:31:50 GMT

One way would be to wrap the same method within a double dow loop. E.g.,:

data want (drop=Monthly_Income

rename=(Locf=Monthly_Income));

do until(last.Student_ID);

set have;

by Student_ID;

if missing(Locf) and not missing(Monthly_Income)

then Locf = Monthly_Income;

end;

do until(last.Student_ID);

set have;

by Student_ID;

Locf = coalesce(Monthly_Income,locf);

output;

end;

run;

Fill missing values with proc expand

warnerj — Thu, 27 Oct 2011 19:55:41 GMT

It worked! Thank you so much!

Re: Fill missing values with proc expand

trich12 — Thu, 11 Oct 2012 14:35:21 GMT

Hi. I had the same missing values problem and used this code and it worked great for me as well. I also tried it on missing character data though, letters and entire words, and I couldn't get it to work. Do you know how I could change this code to work for character data?

Thanks so much!

Re: Fill missing values with proc expand

Haikuo — Thu, 11 Oct 2012 14:44:53 GMT

Maybe because you haven't switched to the right function for character?

Locf = coalesceC(Monthly_Income,locf);

Haikuo

Re: Fill missing values with proc expand

data_null__ — Thu, 11 Oct 2012 14:47:03 GMT

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;

Re: Fill missing values with proc expand

trich12 — Thu, 11 Oct 2012 15:27:12 GMT

Thanks for the suggestions, but I can't get either one to work. I'm sure it's something I am doing wrong. When I try coalesceC, I get errors saying I have invalid numeric data even though I know it's all character data.

When I try the update method, which would be great if I could get it to work because I have several character variables with missing data, it's not filling in the missing data. The first section of that code is just creating the dataset student right? My dataset is called want that contains the missing data and my identifier is cnum, so I just did

data wantLOCF;

update want(obs=0) want;

by cnum;

output;

run;

Is this right? Like I said, I know it's me doing something incorrectly so I really appreciate your help!

Re: Fill missing values with proc expand

data_null__ — Thu, 11 Oct 2012 15:48:12 GMT

Show example of your data.

Re: Fill missing values with proc expand

art297 — Thu, 11 Oct 2012 16:15:18 GMT

DN, I find your approach to LOCF fascinating, but don't understand how it works. Do you have time to provide a brief description of the processing?

TIA,

Art

Re: Fill missing values with proc expand

Haikuo — Thu, 11 Oct 2012 16:43:56 GMT

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

Re: Fill missing values with proc expand

data_null__ — Thu, 11 Oct 2012 17:17:07 GMT

It works because UPDATE does not update/replace a variable value with missing. As long as the missing value is not ._ and there is the NOMISSINGCHECK option but that not what we want here.

We need OUTPUT because the transaction data is actually the data we want not just the LAST obs from the BY list as UPDATE normally outputs.

Can you run data step debugger?

Re: Fill missing values with proc expand

art297 — Thu, 11 Oct 2012 17:54:16 GMT

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

Thanks again!

Re: Fill missing values with proc expand

art297 — Thu, 11 Oct 2012 18:42:25 GMT

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

Re: Fill missing values with proc expand

data_null__ — Thu, 11 Oct 2012 19:14:32 GMT

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.