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
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.
You can always use the import data option.
-- Glenn
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
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
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.
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
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?
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: