How can I force proc import to create all character variables in the output dataset from the Excel workbook?
I can also get a csv version if that makes it easier.
The spreadsheet I'm working with has several hundred columns and most have numeric values, but in some, dashes exist.
CSV would probably make it easier. Is the - the value for missing that is being interpreted as a character for your numeric variables.
If you are importing an EXCEL file and the header row has NAMES then it usually works to use GETNAMES=NO so that it treats the first observation as data.
proc import file=xlsx dbms=xlsx out=want replace;
getnames=no;
run;
You can then use the first observation to generate code to rename the variables if you want and at the same time delete that observation.
proc transpose data=want(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 want(firstobs=2);
rename &renames;
run;
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 25. 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.