DATA Step, Macro, Functions and more

import excel in sas studio

Reply
Contributor
Posts: 22

import excel in sas studio

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

Super User
Posts: 6,935

Re: import excel in sas studio

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.

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

Re: import excel in sas studio

You can always use the import data option.

 

-- Glenn

Contributor
Posts: 22

Re: import excel in sas studio

What is import data option?
Occasional Contributor
Posts: 7

Re: import excel in sas studio

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

Contributor
Posts: 22

Re: import excel in sas studio

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
 
 
Contributor
Posts: 22

Re: import excel in sas studio

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.
Super User
Posts: 6,935

Re: import excel in sas studio

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Super FREQ
Posts: 8,743

Re: import excel in sas studio

[ Edited ]

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

New Contributor
Posts: 2

Re: import excel in sas studio

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?

SAS Super FREQ
Posts: 8,743

Re: import excel in sas studio

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
Super User
Posts: 6,935

Re: import excel in sas studio


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 11 replies
  • 746 views
  • 2 likes
  • 5 in conversation