BookmarkSubscribeRSS Feed
kasman
Calcite | Level 5

I am trying to use libname XLSX to read a file into SAS.  It is pulling in all but the first column.

 

As a background - I have 3 excel files I created in the same program, all with the same columns (but different row content in each one).  I used libname xlsx to output and save these files.

 

I later try to read these into SAS using libname XLSX.  For 2 of them, it creates the SAS datasets with all variables.  For the 3rd one, it is not reading in the first column. 

 

After opening the file in excel, I tried the following changes to the file, and then to read it into SAS (using Libname XLSX):

I tried copying that column and putting it as the last column - still doesn't read in that column. 

I deleted this first column, keeping the copied version at the end - still doesn't read in the column. 

I renamed the column (and file) - still doesn't read it in.

 

When I use proc import - it reads in all columns.

 

Any idea why this is?  These files only have 7 variables, so it is easy to see that one is not being read in.

 

Thanks!

4 REPLIES 4
Tom
Super User Tom
Super User

Tell us more. What are the names of the three columns? Are any of the names longer than 30 characters? Are any of the names invalid as SAS names?

Can you see the data when you open in Excel?  Can you convert the XLSX file to a CSV file using Excel?  Can you read the generated CSV file with SAS? Does it have all of the columns?

kasman
Calcite | Level 5

The variable names in the 3 excel files are all the same.  All are valid SAS variable names.  I can see the data in all 3 excel files when I open them.

 

I can use libname xlsx to read all 3 excel files.  2 of the 3 excel files read in all variables.  It is only this 3rd excel file that is not reading in all of the variables - there is one variable/column that is not being read in, at least when using libname XLSX.  When I use proc import on this 3rd excel file, it reads in all the variables.

 

I haven't tried converting to CSV - as I'm able to read in all variables on 2/3 of the excel files, and proc import seems to work.

ballardw
Super User

Show code for how you are attempting to read things may help. Better would be to run the code reading the file and copy the LOG from that with the code and any messages. Copy from the log and paste into a code window on the forum opened using the {I} icon to preserve text formatting.

Tom
Super User Tom
Super User
You should probably open a ticket with SAS support. They can take your files and try to figure out what the issue is.
Sounds like for now the work around is to use PROC IMPORT instead of the XLSX engine. Are you using PROC IMPORT with DBMS=XLSX or some other DBMS, like EXCEL.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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