BookmarkSubscribeRSS Feed
Louis44
Calcite | Level 5

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.

3 REPLIES 3
PGStats
Opal | Level 21

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
LinusH
Tourmaline | Level 20

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
esjackso
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3404 views
  • 0 likes
  • 4 in conversation