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

Hello,

 

This is more of a general, rather than specific, question that I have run into. I am a fairly new user of SAS and was just wondering if there is a way to format data being read into SAS when using PROC IMPORT for an xlsx file? Does this require a separate DATA step? 

 

For example: if something is being read in as 02/14/2007 (numeric variable formatted automatically as MMDDYY10), is there a way to change this to be 14FEB2007 (formatted DATE9) in the PROC IMPORT step? 

 

Any feedback would be of help! Thanks in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@kirstinpruitt wrote:

Hello,

 

This is more of a general, rather than specific, question that I have run into. I am a fairly new user of SAS and was just wondering if there is a way to format data being read into SAS when using PROC IMPORT for an xlsx file? Does this require a separate DATA step? 

 

For example: if something is being read in as 02/14/2007 (numeric variable formatted automatically as MMDDYY10), is there a way to change this to be 14FEB2007 (formatted DATE9) in the PROC IMPORT step? 

 

Any feedback would be of help! Thanks in advance. 


Not from XLSX or XLS files, especially not if using Proc Import. The procedure uses the values in a small number of rows to set properties.

If the issue is the SAS assigned display format, that can be changed in the SAS data set with a number of other tools: Proc Datasets, data step (caution) or manually, or even just at the time a report where people will read the data is used.

 

If you want to control things when read then you may be better off converting the spreadsheet to a CSV file and then using a data step to read that converted file. If you are going to have multiple files that you expect then the data step allows you to set all the properties and avoid things that happen when relying on proc import, such as variables with the same name and type, or lengths of character variables that can cause other issues.

 

Another possible approach is to use a Libname statement with the spreadsheet and then use a data step to do some standardization of values into a SAS data set and do such there. But this may still have issues with the actual content of the data.

View solution in original post

1 REPLY 1
ballardw
Super User

@kirstinpruitt wrote:

Hello,

 

This is more of a general, rather than specific, question that I have run into. I am a fairly new user of SAS and was just wondering if there is a way to format data being read into SAS when using PROC IMPORT for an xlsx file? Does this require a separate DATA step? 

 

For example: if something is being read in as 02/14/2007 (numeric variable formatted automatically as MMDDYY10), is there a way to change this to be 14FEB2007 (formatted DATE9) in the PROC IMPORT step? 

 

Any feedback would be of help! Thanks in advance. 


Not from XLSX or XLS files, especially not if using Proc Import. The procedure uses the values in a small number of rows to set properties.

If the issue is the SAS assigned display format, that can be changed in the SAS data set with a number of other tools: Proc Datasets, data step (caution) or manually, or even just at the time a report where people will read the data is used.

 

If you want to control things when read then you may be better off converting the spreadsheet to a CSV file and then using a data step to read that converted file. If you are going to have multiple files that you expect then the data step allows you to set all the properties and avoid things that happen when relying on proc import, such as variables with the same name and type, or lengths of character variables that can cause other issues.

 

Another possible approach is to use a Libname statement with the spreadsheet and then use a data step to do some standardization of values into a SAS data set and do such there. But this may still have issues with the actual content of the data.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 457 views
  • 1 like
  • 2 in conversation