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

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

Karem — Tue, 11 Jul 2017 08:45:37 GMT

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

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

RW9 — Tue, 11 Jul 2017 09:41:17 GMT

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;

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

Kurt_Bremser — Tue, 11 Jul 2017 10:08:47 GMT

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.

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

Karem — Wed, 12 Jul 2017 08:57:10 GMT

Thank you! @KurtBremser / @RW9.

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

best regards,

Geof