DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 10

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

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. 

Super User
Posts: 6,946

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

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

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.

Super User
Posts: 6,946

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

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

Version 5.1 (32-bit)

 

Operating System:   WX64_WKS.

Super User
Posts: 6,946

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,407

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

As an alternative to proc import:

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

data want;

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

run;

libname myxlsx clear;

Occasional Contributor
Posts: 10

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

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

Super User
Super User
Posts: 7,407

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

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 @KurtBremser kindly provided, or alternatively, save as CSV and write a datastep import - which to my mind would be a far more robust method.

Occasional Contributor
Posts: 10

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

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

Super User
Posts: 17,868

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

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

 

 

Super User
Posts: 9,682

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

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;
Ask a Question
Discussion stats
  • 11 replies
  • 283 views
  • 0 likes
  • 5 in conversation