How to remove Apostrophe ( ' ) in each observation during PROC IMPORT of XLS file (excel 97-2003)

[Karem]

Hi.

I'm having  trouble in importing XLS file to SAS data set. Proc Import works, but the SAS data set produces all observation as CHAR.

I checked the XLS file (which is software generated file), and i have found that all observation has an APOSTROPHE ( ' ), whether it is

CHAR or NUM header. While (1st Row) Header do not have this apostrophe ( ' ).

How can i removed this apostrophe ( ' ), so that SAS can translate correct observation type in all header ?

Note: Find/Replace in Excel can't find apostrophe ( ' )

XLS Numeric Header: With ( ' )

XLS Char Header: With ( ' )

XLS (First Row) Header: Without ( ' )

CODE:

proc import out= name

datafile= "&fpath"

dbms=xls replace;

getnames=yes;

datarow=2;

mixed=no;

run;

thanks!

Karem

[RW9 · Accepted solution]

Another good example of why Excel is a really poor data medium, these "helpful" Excel bits.  I would suggest you save the file into CSV and then write a datastep to import the data into the model you know - i.e.

data want;
  infile "mydata.csv";
  input status $ x y;
run;

[Kurt_Bremser]

data name_new (rename=(
  status=_status
  x = _x
  y = _y
));
set name;
status = substr(_status,2);
x = input(substr(_x,2),best.);
y = input(substr(_y,2),best.);
drop _status _x _y;
run;

after the import.

Or get rid of the crappy xls format, as @RW9 suggested.

[Karem]

Thank you! @KurtBremser / @RW9.

Converting to csv is simpler considering if there will be a numerous header.

best regards,

Geof