import excel in sas studio

[Ps8813]

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

[Kurt_Bremser]

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.

[glenn_kuly]

You can always use the import data option.

-- Glenn

[Ps8813]

What is import data option?

[glenn_kuly]

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

[Ps8813]

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

[Ps8813]

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.

[Kurt_Bremser]

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.

[Cynthia_sas]

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

[prakash8813]

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?

[Cynthia_sas]

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

[Kurt_Bremser]

---

@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.