Help using Base SAS procedures

Formatting Null Dates in Proc SQL

Reply
Occasional Contributor
Posts: 9

Formatting Null Dates in Proc SQL

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.

Respected Advisor
Posts: 4,927

Re: Formatting Null Dates in Proc SQL

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

PG
Super User
Posts: 5,433

Re: Formatting Null Dates in Proc SQL

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.

Data never sleeps
Super Contributor
Posts: 334

Re: Formatting Null Dates in Proc SQL

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!

EJ

Ask a Question
Discussion stats
  • 3 replies
  • 640 views
  • 0 likes
  • 4 in conversation