fill in values in blank excel cells via sas

[sophia_SAS]

Hi SAS Users,

I'm importing multiple excel spreadsheets into SAS and have blank cells that should have values in them. I'm wondering if there's a way to efficiently fill in the blank cells with the right values via SAS.  Here's my example (ignore date formats).

Thanks!

data have_excel;

input      date id test;

               1/1/2014 AA neg

                            AB neg

                            AC pos

               1/2/2014 BB pos

               1/3/2014 CC neg

                             CD neg

               1/4/2014  DD neg

;;

data wan_SAS;

input      date id test;

               1/1/2014 AA neg

               1/1/2014 AB neg

               1/1/2014 AC pos

               1/2/2014 BB pos

               1/3/2014 CC neg

               1/3/2014 CD neg

               1/4/2014 DD neg

;;

[ballardw]

If you are getting blanks that shouldn't be the first thing would be to go back to the Excel sheet and see what the values are. It is likely that some of the dates have been entered as text and not date values. I would see about exporting the file to CSV and try importing that.

data want;

     set have;

     lagdate= lag(date);

     if date=. then date=lagdate;

     drop lagdate;

run;

Might accomplish what you need.

[sophia_SAS]

The blanks are fields that haven't been filled in though relate to the earliest date listed for that grouping.  The code you supplied does work but only if there is only blank field following a date value.

I know I could set up code for lag2, lag3, lag4 variables, but the number of blanks after each date varies.  How do I set-up lagx that automatically adjusts to each date group?

In my previous example, sometimes I have 2 blanks (ID=AB, AC) following a supplied date value (ID=AA) while other times I have more or less.

[Astounding]

You don't need a ton of lags ... just track the last known value.  For example:

data want;

   set have;

   retain prior_value;

   if date > . then prior_value = date;

   else date = prior_value;

   drop prior_value;

run;

Good luck.

[sophia_SAS]

Thanks!  Code worked great!