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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.