03-04-2013 03:11 PM
I need to import various Excel files into SAS. These files have a Date field that sometimes contains a value, but other times they are all null.
After I import the data, I select all the fields using proc sql. I can format the dates when they exist, but if they're null, then I get an error saying "Character expression requires a character format." Here is my code:
EffectiveDate format yymmddn8.
It looks like the only way to do it is to convert it to a char first, then back to a date. Is there a better way to do this? This is what I'm doing now:
INPUT(CATS(EffectiveDate),yymmddn8.) as EffectiveDate yymmddn8.
03-04-2013 08:13 PM
You could use a datastep and execute different code depending on the type of your column. The VTYPE function would tell you which type ("C" or "N") your imported field is. - PG
03-05-2013 05:04 AM
I suggest that when you import your data, explicitly define you date columns as numeric.
Depending on your import method, this done differently. Please refer to the documentation for your import method.
03-05-2013 08:13 AM
I bet Excel is guessing wrong for the variable when SAS imports it (this is assuming you are using import wizard, proc import, or libname access). As said the best / most reliable way to tackle this is to explicitly read the excel files using a data step (tab delimited should work). That way you can read the values in directly to the form you need them and format them as well.
What is a little confusing to me is that according to the error you are getting the variable is already a character value, but maybe its in part of the code not provided.
Hope this helps!