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.
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
Could it be that you also have the listing destination open with ODS?
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;
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.
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?
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.
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
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;
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: