We are trying to read xls file by using macro xlstosas, using SAS9.2 and xls 2010 versions
We have two version, of the xls file, changed between due to minor differences
The first xls is running o.k. in SAS,The second is providing us the following note:
Thanks we will check this option also
Hi Yaffa,
I am not sure about the reason for this error, but if the 2 versions of the spreadsheet are both saved in the 2010 default format xlsx (or you can save them as such), then they are actually in a xml format and as such in a readable text-format. This means that opening both files in a text editor with a file compare function (like notepad++) or with any file compare utility (like csdiff) makes it possible to compare them and deduce from the differences found, where you can find the actual differences in excel.
It might not be an easy solution, but it might help you, if you are really stuck.
Good luck!
Cloggy
I was unable to find that macro on the web, so without more information you are stuck with Cloggy's recommendation.
One place that Excel has burned me in reading into SAS is that an Excel user can embed a <CR> inside a cell. That will cause the error that you described.
Another hidden character that Excel users can embed is CTRL-Z. If the macro converts the Excel file into text to read it, then SAS will interpret the Crtl-Z as an end-of-file mark and stop processing. That could give the same error message.
Doc Muhlbaier
Duke
Thanks,
I will try to look at this and update with findings,
In any case your answer gave me addtional confirm it's in the xls and not in the SAS,
This is what I found on web,
http://www.pharmasug.org/proceedings/2011/CC/PharmaSUG-2011-CC10.pdf
our problem is in step#3, also below is our problematic section:
/*get names of sheets and put in &SheetNames;*/
data _null_;
length MacCmd $200;
file xlmacro;
%do sh=1 %to &nsheets;
MacCmd="=select(!$b$&sh,!$b$&sh)";
put MacCmd;
put '=set.name("cell",selection())';
%do wn=1 %to &sh;
put '=workbook.next()';
%end;
put '=set.value(cell,get.workbook(3))';
put '=workbook.activate("Macro1")';
%end;
put '=halt(true)';
put '!dde_flush';
file xl2sas;
put '[run("macro1!r1c1")]';
put '[error(false)]';
run;
filename sheets dde "excel|macro1!r1c2:r&nsheets.c2" lrecl=1000;
data tables;
length junk SheetName $500;
infile sheets truncover delimiter=']' ;
input junk SheetName;
/*drop junk;*/
run;
It sounds like you are successfully locating all the sheet names, but getting them read into a SAS data set is a problem. If that is the case, try switching to one long variable instead of trying to parse what is in the sheet:
length line $ 5000;
input @;
line = _infile_;
Besides keeping LINE, also keep the location of the data (name of the sheet, etc.);
Then run a proc compare on the two versions of the SAS data sets. It might be more difficult to locate where the changes are within a 5000-character line, but at least you will find the lines that are different.
Good luck.
Thanks we will check this option also
Hi thank you all for your response we tried the solution of switching to one long variable instead of trying to parse what is in the sheet, this give us some result, the problem is that it enforce us to check the xls each time,
After additional check we found that there is a protection checkbox in xls that was not enables under
Options > Trust Center >Macro Settings > "Trust access to the VBA project object model" , then the program is running o.k.
So indeed it was in the xls
Yaffa
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.