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

[mgrd]

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. 

[Kurt_Bremser]

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.

[mgrd]

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.

[Kurt_Bremser]

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.

[mgrd]

Version 5.1 (32-bit)

Operating System:   WX64_WKS.

[Kurt_Bremser]

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.

[RW9]

As an alternative to proc import:

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

data want;

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

run;

libname myxlsx clear;

[mgrd]

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

[RW9]

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.

[mgrd]

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,

MD

[Reeza]

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

[Ksharp]

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;