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


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:

SAS went to a new line when INPUT statement reached past the end of a line

1. What can be the reason of this error

2. Using Missover, the table provide is empty

3. Is there a way to find the differences (there are too many sheets to look for)

Thanks

Yaffa

1 ACCEPTED SOLUTION

Accepted Solutions
6 REPLIES 6
Cloggy
Calcite | Level 5

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

Doc_Duke
Rhodochrosite | Level 12

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

YaffaR
Calcite | Level 5

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;

Astounding
PROC Star

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.

YaffaR
Calcite | Level 5

Thanks we will check this option also

YaffaR
Calcite | Level 5

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

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
  • 6 replies
  • 3310 views
  • 1 like
  • 4 in conversation