topic Re: Formatting Null Dates in Proc SQL in SAS Procedures

Formatting Null Dates in Proc SQL

Louis44 — Mon, 04 Mar 2013 20:11:36 GMT

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:

SELECT

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:

SELECT

INPUT(CATS(EffectiveDate),yymmddn8.) as EffectiveDate yymmddn8.

Re: Formatting Null Dates in Proc SQL

PGStats — Tue, 05 Mar 2013 01:13:42 GMT

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

Re: Formatting Null Dates in Proc SQL

LinusH — Tue, 05 Mar 2013 10:04:28 GMT

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.

Re: Formatting Null Dates in Proc SQL

esjackso — Tue, 05 Mar 2013 13:13:56 GMT

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!