Print Line of Text to Tagsets.ExcelXp is broken into multiple lines

[SaugataM]

I can use data _null_ and file print, but not matter what, if the text is a bit long it is broken into multiple lines. Also the next line goes into the next worksheet. Below is the code I am running:

ods tagsets.excelxp file='c:\temp\CheckScenario.xls'
options(sheet_name="Tab_Name_here" frozen_headers='5' ascii_dots='no');

Data _null_;		
			 Length Text1 $500. Text2 $500.;
			 Text1 = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. To take a trivial example, which of us ever undertakes laborious physical exercise, except to obtain some advantage from it?";
			 Text2 = "Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.";
			 file print;
				 put #2 @1 "Header goes here";
				 put #3 @1 "The place for Header2 is here ";
				 put #15 @50 "Relevant information goes here. Working";
				 put #16 @1 Text1;
				 put #17 @1 Text2;
run;
ods tagsets.excelxp close;

If you run this code you will get a report that does not follow the correct line structure. In short the Text1 will be broken in two lines and encroach on row number 17. Then the Text 2 is pushed to another tab (created automatically) and starts from that tab's 17th row. How can I fix this and get the texts in single line and maintain line numbers?

Also if I use, option ascii_dots='no' to get rid of the starting period; then line 3 starts from column 1 instead of @50 as mentioned. If I do not use this option then the text start at 50 but I get periods at the start of each row. Any way to fix this?

[Kurt_Bremser · Accepted solution]

Set the linesize for listing output:

options linesize=256;

256 is the maximum value for this.

Consider using ODS EXCEL and a procedure like REPORT with styles.

[Kurt_Bremser]

To simply send text to the ODS destination(s), use the ODS TEXT statement.

[Cynthia_sas]

Hi:
I think you'll have to check with Tech Support, but I do not believe that line pointer and column pointer FILE PRINT (which was designed for the LISTING window) will work with ODS EXCEL. There are other ways to generate the report you want.

Do you intend for all this output to be in one document on one page???

ODS TEXT is one approach, you will not be able to control the @50 placement for the 3rd line. Are you wanting this line to be centered?

Cynthia

[SaugataM]

Ok, I think I should give some context for a holistic view of my problem. I want to print a dataset into xls with 2 titles and 2 footnotes using tagset.excelxp (legacy code). That is pretty simple and works like a charm. The catch is, when the dataset is empty; then the worksheet doesnot get created. The expectation in case of empty dataset is, the titles and footnotes will be same, an extra sentence saying "No records found" will come between title and footnotes. Ideally, I do not need the column headers ideally, but if it comes, I am ok with that too.

If I have to veer out for this from excelxp tagset to ods excel; I have to get a buy in from upper management as that is a bigger change.

Hope, I have been able to give you the full picture.

[ballardw]

You might be attempting more work than needed. Though if you are not using TITLE and FOOTNOTE statements that could be an issue.

One approach would be to have something that tests whether the data set is empty (or exists, different things) and if there are no records then output something else.

Here is an example.

%macro dummyprint(dsname);
proc sql noprint;
   select count(*) into : reccount
   from &dsname.;
quit;
%let reccount = %sysfunc(coalescec(&reccount,0));

%if &reccount=0 %then %do;
   Proc odstext;
   p "No records found";
   run;
%end;
%mend;

data work.junk;
   input x y;
datalines;
;

Title "some title";
Footnote"some footnote";
%dummyprint(work.junk);

proc print data=work.junk;
run;

The dummy print macro (missed in the first paste) only creates output when the given data set name is empty. It also is procedure that Title and Footnote statements recognize so they go around the output. If the Data does have records then the macro doesn't create output. So you could place this before any Print, or other procedure, expected to create output using the proper data set name.

[Cynthia_sas]

Hi:
Well, as long as you're using Macro Programs, you could just make a dataset with 1 variable called "Message" that said "No records found" and then could PROC PRINT the message dataset if the original dataset was empty. I am fairly certain this has been asked and answered before and may even have code posted previously in the Forums.
Cynthia

[ballardw]

---

@Cynthia_sas wrote:
Hi:
Well, as long as you're using Macro Programs, you could just make a dataset with 1 variable called "Message" that said "No records found" and then could PROC PRINT the message dataset if the original dataset was empty. I am fairly certain this has been asked and answered before and may even have code posted previously in the Forums.
Cynthia

---

Proc print doesn't have a nice way, at least that I've found, that won't print some variable label text. Which from context of the OP requirement appears to be undesirable.

[Cynthia_sas]

Well, then you can use PROC REPORT with the NOHEADER option if you don't want the column headers.

  As proof of concept that NOHEADER works:

data norecords;
  message = 'No Records Found';
run;

ods tagsets.excelxp file='c:\temp\norecords.xml'
    style=htmlblue options(embedded_titles='yes'
                           embedded_footnotes='yes');
proc report data=norecords noheader;
title 'Title 1 No Records';
footnote 'Footnote 1 No Records';
  column message;
  define message / display 
     style(column)={font_size=12pt color=red font_weight=bold};
run;

ods tagsets.excelxp close;

 Cynthia

[ballardw]

---

@Cynthia_sas wrote:

Well, then you can use PROC REPORT with the NOHEADER option if you don't want the column headers.

  As proof of concept that NOHEADER works:

data norecords;
  message = 'No Records Found';
run;

ods tagsets.excelxp file='c:\temp\norecords.xml'
    style=htmlblue options(embedded_titles='yes'
                           embedded_footnotes='yes');
proc report data=norecords noheader;
title 'Title 1 No Records';
footnote 'Footnote 1 No Records';
  column message;
  define message / display 
     style(column)={font_size=12pt color=red font_weight=bold};
run;

ods tagsets.excelxp close;

 Cynthia

---

A bit more code than Proc ODSTEXT requires for a single line of text though.

[Cynthia_sas]

true-- but you need the message text in either case and using PROC REPORT gives you more control over the formatting (red and bold) than you get with ODS TEXT. And if you only count PROC REPORT and do not count the ODS statements and the title and footnote which are needed no matter what you do, then the PROC REPORT code is only 4 lines of code: PROC REPORT, COLUMN, DEFINE and RUN.
Cynthia

[ballardw]

---

@Cynthia_sas wrote:
true-- but you need the message text in either case and using PROC REPORT gives you more control over the formatting (red and bold) than you get with ODS TEXT. And if you only count PROC REPORT and do not count the ODS statements and the title and footnote which are needed no matter what you do, then the PROC REPORT code is only 4 lines of code: PROC REPORT, COLUMN, DEFINE and RUN.
Cynthia

---

Just realized that the macro I wrote didn't get pasted into to message.

I am using Proc ODSTEXT, not ODS TEXT, and adding pretty much the same style option after the text as you show in proc report would do the same color, highlight, font etc.

[Cynthia_sas]

Yes, that would work too. The bottom line is that FILE PRINT was designed for ODS LISTING or the old LISTING/OUTPUT window. It is not really working with ODS destinations the way it did with the LISTING window. Basically, I'd recommend abandoning that FILE PRINT approach. There are at least 3 ways to accomplish the desired output: 1) ODS TEXT, 2) PROC ODSTEXT, 3) PROC REPORT.

Cynthia

[SaugataM]

Thanks @Cynthia_sas . Your solution works. The only issue is that I can not place No Records Found in the centre justification. But I can work with that.

[SaugataM]

@ballardw, thanks for your help. But I do not think Proc odstext or ODS Text= works with excelxp tagset though (as per the documentation). I have tried this but it produces excel file with "unable to read".