06-28-2012 05:31 AM
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:
06-28-2012 06:41 AM
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.
06-28-2012 08:43 AM
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.
06-28-2012 08:57 AM
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,
our problem is in step#3, also below is our problematic section:
/*get names of sheets and put in &SheetNames;*/
length MacCmd $200;
%do sh=1 %to &nsheets;
%do wn=1 %to &sh;
filename sheets dde "excel|macro1!r1c2:r&nsheets.c2" lrecl=1000;
length junk SheetName $500;
infile sheets truncover delimiter=']' ;
input junk SheetName;
06-28-2012 09:10 AM
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;
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.
07-02-2012 01:48 AM
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
Need further help from the community? Please ask a new question.