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
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;
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;
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.
Thank you! @KurtBremser / @RW9.
Converting to csv is simpler considering if there will be a numerous header.
best regards,
Geof
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.