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
;;
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.
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.
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.
Thanks! Code worked great!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.