How to standardize the xlsx, excel etc. engines

[AG_Stats]

Hello everyone,

Please anyone consider this small problem and reply a solution:

The following codes are running:

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

proc print data= newlib.sheet1 noobs;/*Or use, proc print data= newlib.'Sheet1$'n; if required (depending on your SAS environment*/
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.

If anyone want to change the engine xlsx (for example, to excel etc.) then he/she can, it does not matter for me.

Ankit

[ballardw]

What does your output look like when you use:

proc print data= newlib.sheet1 noobs label;

run;

And are you running under the University Edition?

[AG_Stats]

I have tried the following codes:

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

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

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

And  got the following output (print):

(Print of sheet1 is taking values as my variable names)

And yes I am using UE of SAS Studio.

[Reeza]

You pretty much can't. 

You our can create a named range in Excel to import data correctly, or convert to CSV. 

Excel ismt a good  way to store or transfer information. See many of @RW9 post on why this is true. 

[Tom]

I have not seen anyway to pass the equivalent of GETNAMES=NO to the XLSX libname engine.

I suspect that you will have to use PROC IMPORT to convert that sheet to a dataset.

[PGStats]

If your environment allows it, you could use

libname newlib Excel '/folders/myfolders/matrix.xlsx' header=no;

[AG_Stats]

No, it is giving following errors:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

55

56

57 libname newlib Excel '/folders/myfolders/matrix.xlsx' header=no;

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

58

59 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

71

[PGStats]

SAS Excel engine relies on Microsoft ACE software. It cannot work unless you have Microsoft Office installed on your machine. SAS xlsx engine doesn't need ACE to work.

[Reeza · Accepted solution]

He's on SAS UE - limited options available.