BookmarkSubscribeRSS Feed
troopon
Calcite | Level 5

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 669 views
  • 0 likes
  • 3 in conversation