BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
markc
Obsidian | Level 7

Hello,

 

I am trying to read in a file in Excel however it won't accept date formats when I use a PROC IMPORT.

 

Of course INFILE does now work with XLSX.

 

How can I best import an external Excel file whereby I can read all character fields with NO TRUNCATION and also be able to specify data formats for the date fields?

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@markc wrote:

Hello,

 

I am trying to read in a file in Excel however it won't accept date formats when I use a PROC IMPORT.

 

Of course INFILE does now work with XLSX.

 

How can I best import an external Excel file whereby I can read all character fields with NO TRUNCATION and also be able to specify data formats for the date fields?

 

Thank you


One of the biggest potential headaches, IMHO, is that way too many Excel files have manually entered/modified data. And since columns of data in Excel may have some cells as numeric valued dates (with a date display format in Excel) and others as text entered with '01Mar2018 or similar then anything reading such garbage has problems.

 

So I tend to turn any XLSX file into a CSV where I can see what sort of stuff exists and use the tools for dealing with delimited text.

Often the exported CSV file will show the mixed types of columns as some having values in quotes (that look like dates) and other values as numeric. Better results may be possible by highlighting a column in Excel and setting the same display type for the entire column before saving to CSV.

 

Truncation and other issues may be an issue because of the (not friendly for data interchange) of having the "alt-enter" to create vertical breaks inside a data cell.

View solution in original post

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

1. What is your truncation problem? See here.

2. Just run proc datasets after importing to change the formats or any other metadata (variable names, labels, etc).

markc
Obsidian | Level 7

Thank you ChrisNZ!

 

I actually wasn't sure if I was having a truncation problem, I just wanted to be 100% certain that I wasn't creating something which would truncate somehow moving forward.    Based on a reply within your link, it seems that using XLSX will look forward through the entire file to determine the max character length of any field to prevent inadvertent truncation, would this be true?

 

With regards to the date issue, when I use PROC IMPORT it seems to be reading the data in format DD/MM/YYYY as $CHAR5 so when I try to use the PROC DATASETS to MODIFY the file, it is not recognising DDMMYY10., in other words when I use:

 

format Effective_Date ddmmyy10.;

 

Do you know what I am doing wrong?

 

Many thanks!

Mark

 

 

ChrisNZ
Tourmaline | Level 20

> would this be true?

I thankfully hardly ever import excel data, but it seems to be the case.

 

 

> Do you know what I am doing wrong?

If the dates are imported as strings, you need to create new *numeric* variables.

This is done in a data step: it involves more than metadata changes and requires creating a new table.

And you use the input function to read the string into a SAS date.

For further details, examples of issues you encounter should be provided.

ballardw
Super User

@markc wrote:

Hello,

 

I am trying to read in a file in Excel however it won't accept date formats when I use a PROC IMPORT.

 

Of course INFILE does now work with XLSX.

 

How can I best import an external Excel file whereby I can read all character fields with NO TRUNCATION and also be able to specify data formats for the date fields?

 

Thank you


One of the biggest potential headaches, IMHO, is that way too many Excel files have manually entered/modified data. And since columns of data in Excel may have some cells as numeric valued dates (with a date display format in Excel) and others as text entered with '01Mar2018 or similar then anything reading such garbage has problems.

 

So I tend to turn any XLSX file into a CSV where I can see what sort of stuff exists and use the tools for dealing with delimited text.

Often the exported CSV file will show the mixed types of columns as some having values in quotes (that look like dates) and other values as numeric. Better results may be possible by highlighting a column in Excel and setting the same display type for the entire column before saving to CSV.

 

Truncation and other issues may be an issue because of the (not friendly for data interchange) of having the "alt-enter" to create vertical breaks inside a data cell.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 4 replies
  • 746 views
  • 2 likes
  • 3 in conversation