topic Re: How to standardize the xlsx, excel etc. engines in SAS Programming

How to standardize the xlsx, excel etc. engines

AG_Stats — Fri, 15 Apr 2016 04:21:49 GMT

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

Re: How to standardize the xlsx, excel etc. engines

ballardw — Fri, 15 Apr 2016 07:25:25 GMT

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?

Re: How to standardize the xlsx, excel etc. engines

Tom — Fri, 15 Apr 2016 08:08:39 GMT

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.

Re: How to standardize the xlsx, excel etc. engines

AG_Stats — Fri, 15 Apr 2016 09:33:33 GMT

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.

Re: How to standardize the xlsx, excel etc. engines

Reeza — Fri, 15 Apr 2016 11:36:08 GMT

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.

Re: How to standardize the xlsx, excel etc. engines

PGStats — Fri, 15 Apr 2016 17:41:53 GMT

If your environment allows it, you could use

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

Re: How to standardize the xlsx, excel etc. engines

AG_Stats — Fri, 15 Apr 2016 18:44:36 GMT

No, it is giving following errors:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

59 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: How to standardize the xlsx, excel etc. engines

PGStats — Fri, 15 Apr 2016 19:38:22 GMT

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.

Re: How to standardize the xlsx, excel etc. engines

Reeza — Fri, 15 Apr 2016 20:12:54 GMT

He's on SAS UE - limited options available.