topic Re: How to import only some columns from a XLSX file into SAS? in SAS Programming

How to import only some columns from a XLSX file into SAS?

mgrd — Thu, 04 Feb 2016 09:50:06 GMT

Hi, I'd like to import some columns from .XLSX file into SAS. I've tried some code posted in the communities but doesn't work! Any idea?

Thanks.

Re: How to import only some columns from a XLSX file into SAS?

Kurt_Bremser — Thu, 04 Feb 2016 09:57:35 GMT

Just do a simple import, and then use a keep statement to keep only the columns you want.

And give some information about what "doesn't work".

Post the code and log containing error messages, structure of dataset as was expected and as came out eventually.

Re: How to import only some columns from a XLSX file into SAS?

RW9 — Thu, 04 Feb 2016 10:25:05 GMT

As an alternative to proc import:

libname myxlsx xlsx "c:\anxlsxfile.xlsx";

data want;

set myxlsx.sheet1 (keep=var1 var2...);

run;

libname myxlsx clear;

Re: How to import only some columns from a XLSX file into SAS?

mgrd — Thu, 04 Feb 2016 10:48:42 GMT

Dear RW9, I've tried your solution but doesn't work, with the error below:

GOPTIONS ACCESSIBLE;

libname myxlsx xlsx "C:\Desktop\Imparidade\Test_Excel.xlsx";
ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.

Tks

Re: How to import only some columns from a XLSX file into SAS?

RW9 — Thu, 04 Feb 2016 10:57:15 GMT

Are you using SAS 9.2? If so you can't use libname xlsx on that version;

http://support.sas.com/kb/49/468.html

You will have to go with the proc import as @Kurt_Bremser kindly provided, or alternatively, save as CSV and write a datastep import - which to my mind would be a far more robust method.

Re: How to import only some columns from a XLSX file into SAS?

Reeza — Thu, 04 Feb 2016 13:42:27 GMT

You can't selectively import data, but you can choose to not keep the variables or drop them in a further step.

Re: How to import only some columns from a XLSX file into SAS?

Ksharp — Fri, 05 Feb 2016 02:05:59 GMT

Use RANGE= option .

Also you could try other dbms:

dbms=excel

dbms=xlsx

proc import datafile='/folders/myfolders/edu.xls' out=have dbms=xls replace;
range='Sheet1$:A1:F10';
run;

Re: How to import only some columns from a XLSX file into SAS?

mgrd — Wed, 17 Feb 2016 11:42:41 GMT

Hi KurtBremser,

I´ve used the codes below to import xlsx files, but the code gives error.

Code:

PROC IMPORT datafile='Z:\Programs\file.xlsx'

OUT=test DBMS=excel replace;

sheet='dw_names;

RUN;

Error: ERROR: DBMS type EXCEL not valid for import.

Code:

PROC IMPORT datafile='Z:\Programs\file.xlsx'

OUT=test DBMS=xlsx replace;

sheet='dw_names;

RUN;

Error: ERROR: DBMS type EXCEL not valid for import.

Do you have some idea to solve this?

Thanks in advance.

Re: How to import only some columns from a XLSX file into SAS?

Kurt_Bremser — Wed, 17 Feb 2016 11:51:13 GMT

Which SAS version are you using?

Edit: irrelevant.

You don't have SAS/ACCESS to PC Files licensed.

Or it is not installed although licensed.

Re: How to import only some columns from a XLSX file into SAS?

mgrd — Wed, 17 Feb 2016 11:49:23 GMT

Hi RW9,

I've test your suggestion but without success. Could you please have a look to error?

Code:

libname myxlsx “Z:\Programs\file.xlsx”;

data want;

set myxlsx.auto_dw_reportdetalhado (keep=CFK WF);

run;

libname myxlsx clear;

Error:

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

18 data want;

19 set myxlsx.auto_dw_reportdetalhado (keep=CFK WF);

ERROR: Libname MYXLSX is not assigned.

20 run;

Thanks,

Re: How to import only some columns from a XLSX file into SAS?

mgrd — Wed, 17 Feb 2016 12:05:53 GMT

Version 5.1 (32-bit)

Operating System: WX64_WKS.

Re: How to import only some columns from a XLSX file into SAS?

Kurt_Bremser — Wed, 17 Feb 2016 12:11:54 GMT

SAS version 5.1 (if there ever was one in production) would be over 30 years old and never work on any Windows. You probably mistook the Enterprise Guide version for this.

If your EG is 5.1, then you have at least SAS 9.2 as the workspace server. This still means that you have a problem with SAS/ACCESS to PC Files.