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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas_lak
Quartz | Level 8


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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

sas_lak
Quartz | Level 8

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

Cynthia_sas
SAS Super FREQ

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

Reeza
Super User

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;

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!

How to Concatenate Values

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.

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
  • 7 replies
  • 4344 views
  • 9 likes
  • 5 in conversation