Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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


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


Accepted Solutions
Solution
‎06-28-2012 09:24 AM
New Contributor
Posts: 4

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

Thanks we will check this option also

View solution in original post


All Replies
New Contributor
Posts: 2

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

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

Trusted Advisor
Posts: 2,113

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

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

New Contributor
Posts: 4

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

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;

Super User
Posts: 5,082

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

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.

Solution
‎06-28-2012 09:24 AM
New Contributor
Posts: 4

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

Thanks we will check this option also

New Contributor
Posts: 4

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 474 views
  • 0 likes
  • 4 in conversation