topic Import Excel File in SAS Programming

Import Excel File

_Hopper — Thu, 08 May 2025 15:06:12 GMT

How do I import data from XLSX into SAS and have all the columns interpreted as text values?

Re: Import Excel File

Tom — Thu, 08 May 2025 15:30:10 GMT

If you are running SAS on Windows and you have a compatible version of Excel then you can use the DBSM=EXCEL setting. That will allow you to use DBSASTYPE= dataset options. And also I think that using the EXCEL libref engine will allow you to use pass thru SQL to query the sheet as if they are a database and do things like CAST() the values.

If you are not and are forced to use the XLSX engine then I think the best you can do is read the sheet with GETNAMES=NO and hopefully the column headers will force SAS to make the variables character. You can use a little code to convert the first observation into the variable names. So you imported the sheet into a dataset named HAVE you can make a dataset named WANT with the names converted using code like:

proc transpose data=have(obs=1) out=names;
  var _all_;
run;
proc sql noprint;
  select catx('=',_name_,nliteral(col1)) into :renames separated by ' '
  from names;
quit;
data want;
  set have(firstobs=2 rename=(&renames));
run;

But watch out for cells with DATE values. Normally when those get read into a character variable you receive the raw values converted to digit strings instead of the formatted values. To covert them into DATE values use code like:

datevar= input(charvar,32.) + '31DEC1899'd ;
format datevar date9.;

Or for DATETIME values use

datetimevar= dhms(input(charvar,32.) + '31DEC1899'd,0,0,0);
format datetimevar datetime19.;

Re: Import Excel File

ballardw — Thu, 08 May 2025 18:06:15 GMT

Is the the only Excel file with this structure that you will import? If not, you might consider saving the file to a text format like CSV and writing a data step to read that text file. That way you specify things like the length of the variables, which will change if using Proc Import as well as controlling how each variable is read. Then reuse the data step by changing the name of the input file and the output data set.

Re: Import Excel File

Ksharp — Fri, 09 May 2025 01:21:36 GMT

As Tom said, using DBSASTYPE= option ,

but you need to list all the variable name manually ,that is very boring.

proc import datafile = "c:\temp\date.xlsx" out =have replace dbms =excel ;
dbdsopts="dbsastype=(age='numeric' weight='char(20)' weight='char(20)')";
run;

libname x excel 'c:\temp\date.xlsx';
data have2;
set x.'date$'n(dbsastype=(age='numeric' weight='char(20)' weight='char(20)'));
run;