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

I used the code below to read an external Excel file and just noticed it only read the first 255 columns.  When I used PROC IMPORT, all 266 columns were read in.  Some of the column names are longer than 32 characters, so PROC IMPORT gave some variable names as VAR15, etc.  When I used the code below, it did not assign names such as this but instead, it would read the first 32 characters and truncate the rest, which makes renaming a bit easier.


Is this expected?  I'm running SAS 9.4 on a PC.

libname datain  'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

data data1;

set datain.'1936-3701-SPF-v1$'n;

run;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

I have to give it a try.

WOW G. I know what is going on . You must misspell the path name or filename .

When I fix path name from /folders/myfolder/x.xlsx  ---> /folders/myfolderS/x.xlsx

I get the result . But when I use WRONG path name ,SAS still show Libname has been assign successfully.It is so weird. That might be a BUG for SAS.

Code: Program

libname X XLSX '/folders/myfolders/x.xlsx'  ;
proc sql;
select nliteral(memname)
  from dictionary.members
   where libname='X' ;
quit;




Log: Program

Notes (2)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

53

54 libname X XLSX '/folders/myfolders/x.xlsx' ;

NOTE: Libref X was successfully assigned as follows:

  Engine: XLSX

  Physical Name: /folders/myfolders/x.xlsx

55 proc sql;

56 select nliteral(memname)

57 from dictionary.members

58 where libname='X' ;

59 quit;

NOTE: PROCEDURE SQL used (Total process time):

  real time 0.05 seconds

  cpu time 0.03 seconds

 

60

61

62

63

64 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

74


Results: Program

SHEET1
SHEET2
SHEET3

And remove $ from your table name ,that could work.

60 data data1;

61 set x.'Sheet1'n;

62 run;

NOTE: The import data set has 1 observations and 2 variables.

NOTE: There were 1 observations read from the data set X.Sheet1.

NOTE: The data set WORK.DATA1 has 1 observations and 2 variables.

NOTE: DATA statement used (Total process time):

  real time 0.03 seconds

  cpu time 0.03 seconds

Xia Keshan

Message was edited by: xia keshan

Message was edited by: xia keshan

View solution in original post

12 REPLIES 12
SASKiwi
PROC Star

I would try the XLSX LIBNAME option which became available in 9.4 M2 to read more than 256 columns.

SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition


libname datain  xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

32 characters is the maximum length for SAS variable names, hence the truncation / rename behaviour is expected.

BTW the SAS option VALIDVARNAME controls whether Excel column names are converted to valid SAS column names or not. Use VALIDVARNAME = V7 to translate, ANY for no translation.

GreggB
Pyrite | Level 9

Not sure what's wrong.  Here's my code and the ERROR statement in the log.  When I use this data step with the libname statement from my original post, everything works.   ...hmmm

libname datain  xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

data data1;

set datain.'1936-3701-SPF-v1$'n;

run;

28   libname datain  xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

NOTE: Libref DATAIN was successfully assigned as follows:

      Engine:        XLSX

      Physical Name: G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx

29   data data1;

30   set datain.'1936-3701-SPF-v1$'n;

ERROR: Couldn't find range or sheet in spreadsheet

ERROR: File DATAIN.'1936-3701-SPF-v1$'n.DATA does not exist.

31   run;

SASKiwi
PROC Star

Try looking in the library DATAIN and see what SAS reports as the sheet names, then use what SAS is showing.

If you are using EG just look in the Server List. In Display Manager the SAS Explorer.

GreggB
Pyrite | Level 9

I went to explorer and I see the SAS set with that name.  When I try to open it, I get a message that says "Multiple SAS files with this name exist. The table named Datain."1936-3701-SPF-V1"n will be opened.  When I click 'OK' I get a message "ERROR: Access of sequential tables is not allowed in this viewer. Datain."1936-3701-SPF-V1"n cannot be opened.  Do you want to select a different table to open?"  When I click 'yes' I get nothing.

Ksharp
Super User

Use DICTIONARY to check the real table name in EXCEL. Maybe what you saw is not really what they are.

proc sql;

select nliteral(memname)

  from dictionary.members

   where libname='DATAIN' ;

quit;

GreggB
Pyrite | Level 9

Ok, in an effort to simplify matters, I renamed the tab in the Excel file = Sheet1.

Code that works:

libname datain  'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

data data2;

set datain.'Sheet1$'n;

run;

Code that does not work:

libname datain  xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

data data1;

set datain.'Sheet1$'n;

run;

Log from code that does not work:

1    libname datain  xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

NOTE: Libref DATAIN was successfully assigned as follows:

      Engine:        XLSX

      Physical Name: G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx

2    data data1;

3    set datain.'Sheet1$'n;

ERROR: Couldn't find range or sheet in spreadsheet

ERROR: File DATAIN.'Sheet1$'n.DATA does not exist.

4    run;

WendyT
Pyrite | Level 9

I have found on our system that a LIBNAME reference to an Excel file needs to be cleared before I can use it again.

Try adding LIBNAME DATAIN CLEAR ; RUN ;

If that clears the error, you may be able to use the other suggestions to read the tab without having to rename it.

Hope that helps...

Wendy

libname datain  'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

data data2;

set datain.'Sheet1$'n;

run;

libname datain clear ;

run ;

libname datain  xlsx 'G:\Departments\Research\test scores\Aspire\2015\1936-3701-SPF-v1.xlsx';

data data1;

set datain.'Sheet1$'n;

run;

libname datain clear ;

run ;

GreggB
Pyrite | Level 9

Thanks for the tip, Wendy, but I got the same error.  I even closed the SAS session and opened a new one and tried it with another Excel file but I get the error.

6    libname a  xlsx 'G:\Departments\Research\test scores\Aspire\2015\proc contents.xlsx';

NOTE: Libref A was successfully assigned as follows:

      Engine:        XLSX

      Physical Name: G:\Departments\Research\test scores\Aspire\2015\proc contents.xlsx

7    data one;

8    set a.'Sheet1$'n;

ERROR: Couldn't find range or sheet in spreadsheet

ERROR: File A.'Sheet1$'n.DATA does not exist.

9    run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.ONE may be incomplete.  When this step was stopped there were 0

         observations and 0 variables.

NOTE: DATA statement used (Total process time):

      real time           0.07 seconds

      cpu time            0.03 seconds

10   LIBNAME a CLEAR ;

NOTE: Libref A has been deassigned.

10 !                   RUN ;

SASKiwi
PROC Star

Try this so you are not explicitly naming the sheets.

libname a  xlsx 'G:\Departments\Research\test scores\Aspire\2015\proc contents.xlsx';

proc datasets library = a;

  copy in = a out = work;

run;

quit;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I know this has been said many times before, but why do you need to use Excel in the first place.  It is not a good format for the storing or transport of data.  As the above posters have mentioned, special characters, mixes of data, unstructured format etc. all go towards making it one of the worse tools to do anything (other than pivot tables) with data.  Get your data into CSV (or XML or simliar).  You can do this in a number of ways, my suggestion is to go back to the datasource and get them to change their export - reason, when the data is mis-interpreted by Excel, which it will be, then its their problem.  You can do it yourself though with SaveAs in Excel, and choose CSV.  Then write a datastep import program to read the data in properly.  If you have multiple sheets then a simple VBA macro will suffice, something like:

Sub tmp()

    i = 1

    For Each s In Workbook.Sheets

        s.SaveAs Filename:="Sheet" + i, FileFormat:=xlCSV

        i = i + 1

    Next

End Sub

Ksharp
Super User

I have to give it a try.

WOW G. I know what is going on . You must misspell the path name or filename .

When I fix path name from /folders/myfolder/x.xlsx  ---> /folders/myfolderS/x.xlsx

I get the result . But when I use WRONG path name ,SAS still show Libname has been assign successfully.It is so weird. That might be a BUG for SAS.

Code: Program

libname X XLSX '/folders/myfolders/x.xlsx'  ;
proc sql;
select nliteral(memname)
  from dictionary.members
   where libname='X' ;
quit;




Log: Program

Notes (2)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

53

54 libname X XLSX '/folders/myfolders/x.xlsx' ;

NOTE: Libref X was successfully assigned as follows:

  Engine: XLSX

  Physical Name: /folders/myfolders/x.xlsx

55 proc sql;

56 select nliteral(memname)

57 from dictionary.members

58 where libname='X' ;

59 quit;

NOTE: PROCEDURE SQL used (Total process time):

  real time 0.05 seconds

  cpu time 0.03 seconds

 

60

61

62

63

64 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

74


Results: Program

SHEET1
SHEET2
SHEET3

And remove $ from your table name ,that could work.

60 data data1;

61 set x.'Sheet1'n;

62 run;

NOTE: The import data set has 1 observations and 2 variables.

NOTE: There were 1 observations read from the data set X.Sheet1.

NOTE: The data set WORK.DATA1 has 1 observations and 2 variables.

NOTE: DATA statement used (Total process time):

  real time 0.03 seconds

  cpu time 0.03 seconds

Xia Keshan

Message was edited by: xia keshan

Message was edited by: xia keshan

Tom
Super User Tom
Super User

What are the actual sheet names in the XLSX file?

Is it possible that once SAS removes crazy characters that Excel accepts in sheetnames that two or more of them look like the same name?

What named ranges does the XLSX file contain?

Is it possible that one or more of them look like the names for the sheets in the XLSX file?

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 12 replies
  • 10328 views
  • 3 likes
  • 6 in conversation