Learning SAS? Welcome to the exclusive online community for all SAS learners.

Why SET X.'Sheet1$'n; is not working.

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Why SET X.'Sheet1$'n; is not working.

[ Edited ]

Hello Everyone,

 

Please someone explain me why the following codes are not workin in my SAS Studio (University Edition)

 

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ; /* This is running. */

 

data IMPORT ;
set x.'Sheet1$'n; /* But this is not running. */
_n+1;
run;

 

It is giving following errors:

/*

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
55
56
57 LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;
NOTE: Libref X was successfully assigned as follows:
Engine: XLSX
Physical Name: /folders/myfolders/Matrix.xlsx
58
59 data IMPORT ;
60 set x.'Sheet1$'n;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File X.'Sheet1$'n.DATA does not exist.
61 _n+1;
62 run;
 
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.IMPORT may be incomplete. When this step was stopped there were 0 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds

*/

 

However, Proc import is working..

i.e. the following codes are working: (i.e. my raw data file is at right location/place and sheet name is also correct)

 

FILENAME ago "/folders/myfolders/Learning/Matrix.xlsx" TERMSTR=CR;

PROC IMPORT DATAFILE=ago
DBMS=XLSX
OUT=WORK.IMPORT10;
GETNAMES=No;
SHEET="Sheet1";
RUN;
proc print data= import10 noobs; run;

 

 

Now, instead of using proc import again to do some procedures on Sheet2. Can I use some short cuts to save steps. Like for example: 

 

Note: But the following codes are not working: 

 

LIBNAME x XLSX "/folders/myfolders/Matrix.xlsx" ;

proc print data= x.'sheet2$'n;
run;

 

 

/* They are giving again the smae errors. */

 

Thanks 

Ankit

 

Regards,
AG_Stats

Accepted Solutions
Solution
‎04-16-2016 03:09 PM
Super User
Posts: 19,772

Re: Why SET X.'Sheet1$'n; is not working.

If you assign a libname to an Excel file you can navigate to the Library pane and see the name of the sheets.

 

If the table name has $ sign in it, you need to use a name literal to reference the sheet. Examples of this include sheets with spaces in the name, or named ranges. 

 

Otherwise you can access it as you would a standard SAS table and no name literal reference is required.

 

 

View solution in original post


All Replies
Super User
Posts: 19,772

Re: Why SET X.'Sheet1$'n; is not working.

Post a screenshot of the x library. 

 

You can navigate to it on the server library window on the left hand side. 

Contributor
Posts: 65

Re: Why SET X.'Sheet1$'n; is not working.

Hi Reeza, please find the required screenshort.

Screenshort1.png

Regards,
AG_Stats
Super User
Posts: 19,772

Re: Why SET X.'Sheet1$'n; is not working.

[ Edited ]

See the libraries section on the left hand menu. (Tasks/Snippets/Libraries)

Expand that, and then the corresponding X library that you've assigned. Post a screenshot of that.

The current screenshot doesn't provide any additional information from your original post, and not what I asked for Smiley Happy

 

 I would also consider using a different libname since X is a command in SAS.

 

 

Contributor
Posts: 65

Re: Why SET X.'Sheet1$'n; is not working.

Hi Reeza,

 

PF the required attachment (see this time I used newlib instead of X) and also see the required query in this screenshort (but it is too big, even big from proc import)

Screenshort2.png

Regards,
AG_Stats
Super User
Posts: 19,772

Re: Why SET X.'Sheet1$'n; is not working.

I'm confused.

 

Ok, you used Newlib, did that work or not?

What's "too big"?

 

Here you reference Sheet2, not sheet1 as well. 

 

 

 

Contributor
Posts: 65

Re: Why SET X.'Sheet1$'n; is not working.

Hi Reeza,

 

Liabrary newlib is present in MY LIBRARIES but again the following codes are not working:

 

ibname newlib xlsx '/folders/myfolders/learning/matrix.xlsx';

data IMPORT ;
set newlib.'Sheet1$'n;
_n+1;
run;

 

 

And by again it's too big I mean that I have runned the query and it is working but big like proc import was big (two to four lines) and I was interested in short steps.

 

Now fortunately I get idea from SQL query and the following codes are now working:

 

libname newlib xlsx '/folders/myfolders/learning/matrix.xlsx';

data IMPORT ;
set newlib.Sheet1;
_n+1;
run;

 

 

But now I have some different problem. Consider the following codes:

 


proc print data= newlib.sheet1 noobs;
run;


proc print data= newlib.sheet2 noobs;
run;

 

Both are now running but, in first sheet I have data from first row (some numeric observations), and proc print is taking first row as variables names. How can I define that there are no variable names (in Sheet1)  like in porc import we uses - getname option = No.

 

Thanks 

Ankit

Regards,
AG_Stats
Contributor
Posts: 65

Re: Why SET X.'Sheet1$'n; is not working.

[ Edited ]

Okay, if  <libref>.'Sheet1$'n; is not working on a particular SAS Environment then use <liabref>.Sheet1

 

For example if: 

  

ibname newlib xlsx '/folders/myfolders/learning/matrix.xlsx';

data IMPORT ;
set newlib.'Sheet1$'n;
_n+1;
run;

 

Is not working; then use:

 

ibname newlib xlsx '/folders/myfolders/learning/matrix.xlsx';

 

data IMPORT ;
set newlib.Sheet1;
_n+1;
run;

 

This will work but your first row of your sheet must be column headings (otherwise values of first row of the data will become Column headings)

 

Regards,
AG_Stats
Solution
‎04-16-2016 03:09 PM
Super User
Posts: 19,772

Re: Why SET X.'Sheet1$'n; is not working.

If you assign a libname to an Excel file you can navigate to the Library pane and see the name of the sheets.

 

If the table name has $ sign in it, you need to use a name literal to reference the sheet. Examples of this include sheets with spaces in the name, or named ranges. 

 

Otherwise you can access it as you would a standard SAS table and no name literal reference is required.

 

 

Contributor
Posts: 65

Re: Why SET X.'Sheet1$'n; is not working.

Yes, Thanks Reeza, you have given a generalised solution to my problem.

Regards,
AG_Stats
Contributor
Posts: 65

Re: Why SET X.'Sheet1$'n; is not working.

A quite long time has been passed nobody has replied to my post, even you.

Do you need datafile?

I have attached it now.

 

Now the following codes are running:

 

libname newlib xlsx '/folders/myfolders/learning/matrix.xlsx';

proc print data= newlib.sheet1 noobs;
run;
proc print data= newlib.sheet2 noobs;
run;

 

Both are running but in sheet1 I have no variable names / column headings. But newlib.sheet1 taking first row of my sheet as headings.

So, can I control these things (as we do same in Proc Import by using option getnames=yes/no) as in sheet1 I have no headings while in sheet2 I have.

 

Ankit

Regards,
AG_Stats
Super User
Posts: 19,772

Re: Why SET X.'Sheet1$'n; is not working.

I couldn't follow your last post.

No, you don't have a huge amount of control when importing Excel files.

Also, it seems like a new question, and if so you should post a new question.
☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 1565 views
  • 1 like
  • 2 in conversation