topic Re: fill in values in blank excel cells via sas in SAS Programming

fill in values in blank excel cells via sas

sophia_SAS — Wed, 20 Aug 2014 14:29:23 GMT

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

;;

Re: fill in values in blank excel cells via sas

ballardw — Wed, 20 Aug 2014 15:19:59 GMT

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.

Re: fill in values in blank excel cells via sas

sophia_SAS — Wed, 20 Aug 2014 15:28:07 GMT

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.

Re: fill in values in blank excel cells via sas

Astounding — Wed, 20 Aug 2014 15:34:57 GMT

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.

Re: fill in values in blank excel cells via sas

sophia_SAS — Wed, 20 Aug 2014 16:03:54 GMT

Thanks! Code worked great!