Converting date formats

[troopon]

Hi

I have imported a bunch of excel files with identical fields into SAS. SAS has kindly decided that some of the date fields are text strings and some numeric. In the excel files they are all the same format. I know I could use a long infile statement and convert the excel files to CSV but there should be a simple way ro convert the date field into a numberic one.

data output;
set input;
new_date= input(old_date,MMDDYY10.);
run;

I just get an error with the above saying "Invalid argument to function input".

I tried 

data output;
set input;
format old_date MMDDYY10.;
run;

This gives the same error message. Why aren't either of these working. Shouldn't be this hard to tell sas this is a numeric date field.

Thanks

[RW9]

Hi,

This question has been asked many times before.  Take a look at this post:

https://communities.sas.com/t5/Base-SAS-Programming/IMPORT-EXCEL-SHEET-WITH-PROPER-INFORMAT/m-p/2308...

Where I explain why importing data from an unstructured source like Excel, using guessing procedures is a recipe for disaster.

Simple answer, recieve your data in a proper data transfer format, and write an import program based on the agreed data transfer documents.

If you do go down the route of converting imported data afterwards (and I personally wouldn't), then please post a datastep with some example data matching your data.  It is likely that the old_date is either a numeric, or contains data which is not possible to convert.

[ballardw]

Run proc contents on your dataset input and report the results for your dataset input.

NOTE: SAS did not decide they were text fields, the EXCEL engine told SAS that when using Proc import. One thing that can help, AND may be needed even with CSV, is in Excel to select all columns with dates and set the cell formats to the same date format.