Import Excel File

[_Hopper]

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

[Tom]

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

[ballardw]

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.

[Ksharp · Accepted solution]

As Tom said, using DBSASTYPE= option ,

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

1)

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

2)

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