topic Re: import excel in sas studio in SAS Programming

import excel in sas studio

Ps8813 — Tue, 13 Sep 2016 10:49:51 GMT

Hi iam trying to import 1 xlsx file in SAS studio and there is only 1 sheet 'sheet1'.

Iam using below code:-

proc import datafile='/home/prakashnegi0/test.xlsx' out=test
dbms=excel replace;
getnames=yes;
run;

In dbms i tried Excel,xls,excelcs,csv .. but none are working.

Can any one tell me the correct option here.

Thanks

Re: import excel in sas studio

Kurt_Bremser — Tue, 13 Sep 2016 11:43:55 GMT

Define "not working".

Post the log if errors happen, or show expected and actual results of the import if the import goes through but produces an unexpected result.

Re: import excel in sas studio

glenn_kuly — Tue, 13 Sep 2016 11:59:25 GMT

You can always use the import data option.

-- Glenn

Re: import excel in sas studio

Ps8813 — Tue, 13 Sep 2016 11:59:30 GMT

ERROR: DBMS type EXCEL not valid for import.

DBMS=xls :::----

Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later

Requested Input File Is Invalid

Re: import excel in sas studio

Ps8813 — Tue, 13 Sep 2016 12:00:48 GMT

What is import data option?

Re: import excel in sas studio

Ps8813 — Tue, 13 Sep 2016 12:01:04 GMT

ERROR: DBMS type EXCEL not valid for import.

DBMS=xls :::----

Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later

Requested Input File Is Invalid.

Re: import excel in sas studio

Kurt_Bremser — Tue, 13 Sep 2016 12:12:28 GMT

Do you work with SAS University Edition? Or with SAS installed on a non-Windows server?

If yes, try DBMS=XLSX

Discard the above. I see you have a real home directory, so it's not SAS UE, but SAS on UNIX. DBMS=EXCEL requires Windows, DBMS=XLSX is platform-independent. So use XLSX, as suggested.

Re: import excel in sas studio

glenn_kuly — Tue, 13 Sep 2016 12:10:49 GMT

Look at the 'New Options' drop down menu to the right of search on the top bar.

You can select your Excel file with 'New Import Data,' and automatically create a temporary dataset from your spreadsheet.

-- Glenn

Re: import excel in sas studio

Cynthia_sas — Tue, 13 Sep 2016 13:44:40 GMT

This path name:
'/home/prakashnegi0/test.xlsx' is the type of path that is used on the SAS OnDemand for Academics server....which is a Unix system. I do not believe that DBMS=Excel will work with the OnDemand server -- so yes, it is a real home directory, but it is on a Unix/Linux system, so DBMS=XLSX should be used.

cynthia

Re: import excel in sas studio

prakash8813 — Tue, 13 Sep 2016 15:12:47 GMT

Thanks... dbms=xlsx is working in SAS studio.

But i can't see its aletrnate infile statement. i.e. whenever we run proc import , sas automatically generate its aletrnate infile data step code.

But in this case there is no such infile datastep in log. why so?

Re: import excel in sas studio

Cynthia_sas — Tue, 13 Sep 2016 18:04:07 GMT

Hi:
I believe the XLSX engine uses a "back-door" method to communicate directly with Excel for the import. So, thus, no INFILE statement program is needed. That is the take-away I get from reading this BLOG post: http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

cynthia

Re: import excel in sas studio

Kurt_Bremser — Wed, 14 Sep 2016 05:18:39 GMT

@prakash8813 wrote:

Thanks... dbms=xlsx is working in SAS studio.

But i can't see its aletrnate infile statement. i.e. whenever we run proc import , sas automatically generate its aletrnate infile data step code.

But in this case there is no such infile datastep in log. why so?

The .xlsx format is basically XML (stored with zip compression). SAS can treat that (with the right XML map) like a database, while OTOH directly reading with a datastep would render very complex code (the data has to be read like a stream, and the tags need to be resolved as they come; the data step is best suited for text files that deliver data one line per item)

So, proc import here works as if you defined a libname on the Excel file and read from that, leaving data conversion to the XLSX libname engine.