Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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

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 ( ' )

numcol.PNG

 

XLS Char Header: With ( ' )

charcol.PNG

 

XLS (First Row) Header: Without ( ' )

headcol.PNG

 

 

CODE:

proc import out= name

datafile= "&fpath"

dbms=xls replace;

getnames=yes;

datarow=2;

mixed=no;

run;

 

 

thanks!

 

Karem


Accepted Solutions
Solution
‎07-12-2017 04:55 AM
Super User
Super User
Posts: 7,997

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

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;

View solution in original post


All Replies
Solution
‎07-12-2017 04:55 AM
Super User
Super User
Posts: 7,997

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

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;
Super User
Posts: 7,863

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

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

Posted in reply to KurtBremser

 

 

Thank you! @KurtBremser / @RW9.

 

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

 

 

 

best regards,

 

Geof

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 169 views
  • 0 likes
  • 3 in conversation