topic Re: PROC IMPORT - force all values to character in SAS Programming

PROC IMPORT - force all values to character

_Hopper — Fri, 23 Aug 2024 22:47:14 GMT

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.

Re: PROC IMPORT - force all values to character

Reeza — Fri, 23 Aug 2024 23:02:20 GMT

CSV would probably make it easier. Is the - the value for missing that is being interpreted as a character for your numeric variables.

Re: PROC IMPORT - force all values to character

Tom — Sun, 25 Aug 2024 17:50:03 GMT

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;