BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Karem
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User
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
Calcite | Level 5

 

 

Thank you! @KurtBremser / @RW9.

 

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

 

 

 

best regards,

 

Geof

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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