Help using Base SAS procedures

proc import vs. libname when reading an xlsx file

Accepted Solution Solved
Reply
Super Contributor
Posts: 279
Accepted Solution

proc import vs. libname when reading an xlsx file

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;


Accepted Solutions
Solution
‎07-17-2015 09:07 AM
Super User
Posts: 10,023

Re: proc import vs. libname when reading an xlsx file

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


All Replies
Super User
Posts: 3,252

Re: proc import vs. libname when reading an xlsx file

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.

Super Contributor
Posts: 279

Re: proc import vs. libname when reading an xlsx file

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;

Super User
Posts: 3,252

Re: proc import vs. libname when reading an xlsx file

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.

Super Contributor
Posts: 279

Re: proc import vs. libname when reading an xlsx file

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.

Super User
Posts: 10,023

Re: proc import vs. libname when reading an xlsx file

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;

Super Contributor
Posts: 279

Re: proc import vs. libname when reading an xlsx file

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;

Frequent Contributor
Posts: 91

Re: proc import vs. libname when reading an xlsx file

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 ;

Super Contributor
Posts: 279

Re: proc import vs. libname when reading an xlsx file

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 ;

Super User
Posts: 3,252

Re: proc import vs. libname when reading an xlsx file

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;

Super User
Super User
Posts: 7,942

Re: proc import vs. libname when reading an xlsx file

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

Solution
‎07-17-2015 09:07 AM
Super User
Posts: 10,023

Re: proc import vs. libname when reading an xlsx file

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

Super User
Super User
Posts: 7,039

Re: proc import vs. libname when reading an xlsx file

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?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 2604 views
  • 2 likes
  • 6 in conversation