DATA Step, Macro, Functions and more

Printing Error while creating ods xls file in SAS 9.3

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Printing Error while creating ods xls file in SAS 9.3

Hi All,

While creating XLS file, am getting the below warning messages in my log.

Variable having 1000 length.

EX:-

ods html file="C:\Users\lakshman\Desktop\New_folder\XXXX_Warning.xls";

proc sql;

title " Print it ";

select Variable_print from have;

quit;

ods html close;

WARNING: Data too long for column "Variable_print"; truncated to 87 characters to fit.

WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed

Is there any System option to print and get complete data in XML file.

Thanks in Advance.


Accepted Solutions
Solution
‎07-22-2014 06:29 PM
SAS Super FREQ
Posts: 8,866

Re: Printing Error while creating ods xls file in SAS 9.3

Hi:

  Just a few points of clarification. Reeza and Kurt are both correct...that message usually comes from having the LISTING destination open which has "hard" limits on "pagesize" and "linesize" that must be warned when you are about to exceed the limit. So generally, the solution to this WARNING message is merely to close ODS LISTING destination, as Reeza gave in her example.

  Also, being somewhat curmudgeonly about this point, when you use ODS HTML, you are NOT, NOT, NOT creating a "true, binary Excel" file when you name the file with an XLS extension. So you cannot really say "While creating XLS file..." It is more accurate to say, "While creating an HTML file, that I named XLS in order to fool the Windows registry to launch Excel when I click on the file...." When you use ODS HTML, you are creating an HTML ASCII text file which contains HTML markup tags. Ever since Office 97, Excel and Word have been able to open HTML files. And they display the HTML file as though it is a sheet or is a Word doc, but the underlying file type is HTML. You can prove this to yourself by opening the file created by ODS HTML in Notepad or a text editor (not WORD, not EXCEL) -- and then you will see HTML tags inside the file, which shows you that the file extension you gave did not change the underlying nature of the file -- merely fooled the Windows registry into launching Excel when someone double clicks on the file.

cynthia

View solution in original post


All Replies
Super User
Posts: 7,782

Re: Printing Error while creating ods xls file in SAS 9.3

Could it be that you also have the listing destination open with ODS?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,955

Re: Printing Error while creating ods xls file in SAS 9.3

Not sure what you are doing there. You should be using ods with output procedures, SQL is not an output procedure.  Try:

ods tagsets.excelxp file="...\xyz.xls" style=statistical;

proc report data=have nowd;

     column variable_print;

     define variable_print / "Label";

run;

ods tagsets.excelxp close;

Contributor
Posts: 46

Re: Printing Error while creating ods xls file in SAS 9.3


Hi RW9,

I tried report procedure and print procedure also.

finally I used the proc sql, to print the data as it is into xls file.

from your code am getting error message like

ERROR: The width of  "variable_print" is not between 1 and 91. Adjust the column width or line size.

I am looking for, how can I get 1000 length of data into xls file without error and warning messages.

Thanks for quick help.

Super User
Super User
Posts: 7,955

Re: Printing Error while creating ods xls file in SAS 9.3

Please try:

ods listing close;

data test;
  attrib avar avar2 format=$2000.;
  avar=repeat("A",2000);
  avar2=repeat("B",2000);
  output;
run;

ods tagsets.excelxp file="s:\temp\rob\xyz.xls" style=statistical;
proc report data=test nowd;
     column avar avar2;
     define avar / "Label";
     define avar2 / "Label";
run;
ods tagsets.excelxp close;

Does this work in a clean SAS session?

Contributor
Posts: 46

Re: Printing Error while creating ods xls file in SAS 9.3

RW9,

Thank you very much for your valuable information.

But for urgent basis I Exported the data into txt file and submitted.

Any way.. next time Definitely i will try this.

Thank you for quick response. Smiley Happy

Solution
‎07-22-2014 06:29 PM
SAS Super FREQ
Posts: 8,866

Re: Printing Error while creating ods xls file in SAS 9.3

Hi:

  Just a few points of clarification. Reeza and Kurt are both correct...that message usually comes from having the LISTING destination open which has "hard" limits on "pagesize" and "linesize" that must be warned when you are about to exceed the limit. So generally, the solution to this WARNING message is merely to close ODS LISTING destination, as Reeza gave in her example.

  Also, being somewhat curmudgeonly about this point, when you use ODS HTML, you are NOT, NOT, NOT creating a "true, binary Excel" file when you name the file with an XLS extension. So you cannot really say "While creating XLS file..." It is more accurate to say, "While creating an HTML file, that I named XLS in order to fool the Windows registry to launch Excel when I click on the file...." When you use ODS HTML, you are creating an HTML ASCII text file which contains HTML markup tags. Ever since Office 97, Excel and Word have been able to open HTML files. And they display the HTML file as though it is a sheet or is a Word doc, but the underlying file type is HTML. You can prove this to yourself by opening the file created by ODS HTML in Notepad or a text editor (not WORD, not EXCEL) -- and then you will see HTML tags inside the file, which shows you that the file extension you gave did not change the underlying nature of the file -- merely fooled the Windows registry into launching Excel when someone double clicks on the file.

cynthia

Super User
Posts: 19,804

Re: Printing Error while creating ods xls file in SAS 9.3

The error is being generated by the ODS LISTING destination, the default on most SAS installations, rather than the ODS HTML destination.

Turn off the ODS LISTING destination before running that report to verify.

ODS LISTING CLOSE;

And then turn it back on afterwards.

ODS LISTING;

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 1336 views
  • 8 likes
  • 5 in conversation