topic Re: Proc Report Byline Problem in ODS and Base Reporting

Proc Report Byline Problem

OS2Rules — Tue, 12 Jul 2011 17:16:40 GMT

Hi to all:

I have this lovely report that I spent a lot of time on, that uses PROC PRINT to create a hard copy report.

My client has now requested that the report be saved a s an Excel spreadsheet and look exactly the same :smileyalert:

My problem is that the current hardcopy report has a BYLINE and I can't seem to reproduce it using ODS and EXCELXP.

Some info: I am using SAS 9.1.3 on Windows using ODS and tagsets EXCELXP v1.85.

I have "sheet_interval=none" in my EXCELXP options, I am using PROC PRINT and PROC REPORT to generate the report to a file (just to see if one had the error and the other didn't). I have the same "BY" statement as in the hard copy code.

Here is the odd thing - if I have "OPTION NOBYLINE", the resuting spreadsheets (both) are readable, but (obviously) there is no BY line. If I code "OPTION BYLINE", I am unable to open either spreadsheet with that stupid (!!!) "Problems During Load" XML error on the "Table".

Is there a way to get the BY line to print on the report? I assume that it can be done with a BREAK BEFORE in PROC REPORT, but I would like it to look the same as in the original PROC PRINT.

PROC REPORT CODE:

* option byline; *<----<< causes the problem *;

ods listing close;

run;

ods tagsets.ExcelXP

options(sheet_name='Summary'

orientation='landscape'

papersize='LETTER'

embedded_titles='yes'

embedded_footnotes='yes'

FitToPage='yes'

sheet_interval='None'

suppress_bylines='no'

zoom='90'

ROW_HEIGHT_FUDGE='1'

absolute_column_width='12,12,12,12,12,12,12,12,12,12,12');

proc report data=reptdata nowd split='*' missing

style(REPORT)={font_face=Arial font_size=9pt}

style(HEADER)={background=#eeeeee}

style(SUMMARY)={font_face=Arial font_size=9pt};

BY var1 notsorted;

column var1 var2 var3 ...snip... var11;

define var1 / group noprint;

define var2 / display 'label2';

define var3 / display 'label3';

....snip....

define var11 / display 'label11';

rbreak after / summarize;

compute net;

if _break_= '_RBREAK_' then var11 = var10.sum - var9.sum;

else var11 = .;

endcomp;

TITLE1 j=c "FOR &rptdate";

RUN;

ods tagsets.excelxp close;

run;

Proc Report Byline Problem

data_null__ — Tue, 12 Jul 2011 23:20:12 GMT

Try using OPTION NOBYLINE and put your BYLINE in a title.

Title2 '#BYLINE';

may work or you may need to do something with the #BYVAL and #BYVAR.

Just guessing cause I can't test you code. No data.

Proc Report Byline Problem

OS2Rules — Wed, 13 Jul 2011 12:28:21 GMT

Thanks for the suggestion.

The problem with putting the "byline" in the title is that the origianl (using PROC PRINT) will span the byline across the width of the report and I can't replicate this with a title - the variables in the byline have different lengths and this will change the lengthof the line when output.

What I would need is a "mask" of "-" (minus sign) and then lay the by variable on top (cenetered) - then print this as the byline, but that is much too complicated.

I guess my original questions stands - why does the "by" statement not create a "byline" with ODS and EXCELXP ?

Re: Proc Report Byline Problem

data_null__ — Wed, 13 Jul 2011 13:37:11 GMT

I don't understand you talk about PROC PRINT but your example has PROC REPORT. I've tested both and cannot find a problem using BYLINE or NOBYLINE. So maybe your problem is something else.

proc sort data=sashelp.class out=class;
   by age;
   run;
options byline=1;
ods listing close;
ods tagsets.excelxp file='testby.xml'
options(sheet_name='Summary'         
              orientation='landscape'  
              papersize='LETTER'       
              embedded_titles='yes'   
              embedded_footnotes='yes'   
              FitToPage='yes'                              
              sheet_interval='None'                
              suppress_bylines='no'               
              zoom='90'                           
              ROW_HEIGHT_FUDGE='1'     
              absolute_column_width='12,12,12,12,12,12,12,12,12,12,12');  
title1 "With: %sysfunc(getoption(byline,keyword))";
*title '#byline';
 proc report data=class list nowd split='*' missing 
       style(REPORT)={font_face=Arial font_size=9pt} 
       style(HEADER)={background=#eeeeee}            
       style(SUMMARY)={font_face=Arial font_size=9pt};
   BY age notsorted;       
   define _all_ / display;
   run;
ods tagsets.excelxp close;

Re: Proc Report Byline Problem

OS2Rules — Wed, 13 Jul 2011 14:07:34 GMT

Hi:

I tried what you have in your sample code including the fancy macro function, and...

I get about 5,000 of these in that stupid (!!) hidden (!!!!) XML error file:

XML ERROR in Table

REASON: Bad Value

FILE: G:\SAS\Data\DEV\Unmatched_Rpt.xls

GROUP: Row

TAG: Cell

ATTRIB: StyleID

VALUE: byline__c

I'm using version 1.86 of the ExcelXP tagset - which version are you using?

Re: Proc Report Byline Problem

data_null__ — Wed, 13 Jul 2011 15:03:31 GMT

I think you need to download the newest version. I'm using 1.94, I don't know if it is newest.

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12). Add options(doc='help') to the

I see I am far behind the curve as v1.116, 08/25/10 is the latest version offered for download.

Re: Proc Report Byline Problem

Gatineau1 — Wed, 13 Jul 2011 15:13:07 GMT

Hi,

Instead of Titles, you can try:

compute before _page_;

@line1 '#byline';

endcomp;

Sorry, I unable to test your code since I am in 8.2 and ODS Tagset is V9.

Thanks,

Robert,

Re: Proc Report Byline Problem

OS2Rules — Wed, 13 Jul 2011 16:04:31 GMT

Gatineau1:

I tried your suggestion of putting the BY line in the compute block - now it generates 6,000 lines of error messages in the XML error log. (I'd use a emoticon "grin" here if I didn;t think them so stupid).)

Data_null_:

Unfortunately, our production system has v1.86 of the tagset - we are a little behind the times (I'm still running SAS 9.1.3 in "evaluation" mode). Now that 9.3 is out, perhaps we will get some action ....

Anyhow - I'm going to try running with the latest tagset that I have (I think it is v1.116) - in test only - just to see what the results are.

Thanks to all so far.

Re: Proc Report Byline Problem

data_null__ — Wed, 13 Jul 2011 16:15:35 GMT

Even if you can't "install" the newer version on your production system you can %INC 'excltags.tpl'; in your program. I do that in a program that I support that can be run by others users where I don't know if they have the version of the tagset that works for my program.

Re: Proc Report Byline Problem

Cynthia_sas — Thu, 14 Jul 2011 13:09:12 GMT

Hi:

Perhaps this code will help put things in perspective. Options BYLINE is used to tell SAS that you want the "normal" BY group behavior in reports of the dashed divider line (in LISTING) or some kind of divider between BY group sections of your report. The NOBYLINE option is what you would use if you wanted to use the special tokens like #BYLINE or #BYVAL1 in your SAS TITLE statement. When you move to TAGSETS.EXCELXP as an alternative to the LISTING window or OUTPUT window version of your report, then you generally need to use internal EXCELXP xub-options if you want to insert #BYLINE into the worksheets or use the #BYVAL for sheet names.

In the code below, OPTIONS BYLINE is working for me to allow TAGSETS.EXCELXP to send the BYLINE info to the TAGSETS.EXCELXP template for handling and then the suppress_bylines='no' suboption has the impact of putting the Country=CANADA or Country=GERMANY (the BYLINE) into each sheet. The TITLE also uses #BYVAL1 -- so if I want to see the title in sheet (instead of the byline), then I need to have embedded_titles='yes' as a sub-option.

I find it easier to format the title the way I want using TITLE statement options and the TITLE spans the table on each sheet. If I had OPTIONS NOBYLINE, for example, I would still get the #BYVAL1 info in the title, but would not get BYLINE in each sheet.

Version info: SAS 9.2 and Excelxp 1.94.

cynthia

proc sort data=sashelp.prdsale;
by country region division;
run;

ods listing close;
ods graphics off;
options byline;

ods tagsets.excelxp file="c:\temp\use_byxy.xml"
    style=sasweb
    options(sheet_name='#byval1'
            suppress_bylines='no'
            embedded_titles='yes'
            absolute_column_width='12');

** change suppress_bylines to 'yes' if you want to;
** stick with title method;
title;
proc report data=sashelp.prdsale nowd;
    by Country;
    title 'From Title Statement: #byval1';
    column region division prodtype actual;
    define region / group;
    define division / group;
    define prodtype / group;
    define actual/sum;
    define predict/sum;
run;
ods _all_ close;

ods listing;

/* NOTE: Need to use at least the Excel XP tagset

(Compatible with SAS 9.1.3 and above, v1.94, 09/09/12).

-- Look in log for version text will look like above line*/

Re: Proc Report Byline Problem

OS2Rules — Thu, 14 Jul 2011 14:16:27 GMT

Data_null_;

I can change the code in test to point to the newer tagset, but I can't do that when the job runs in our production environment.

In fact I did use tagset v1.116 and I got a byline - but the format was nothing near what I expected. Besides, iit would take too long for me to get this tagset promoted to our prodcution environment than it is worth.

Cyntha:

Thanks for the help - I had to bite the bullet and code the BYLINE valuies in a TITLE statement, as in:

data reptdata2;

set reptdata;

attrib byline length=$240. format=$char240.;

byline = repeat('-',240);

a = length(left(trim(var1))) + length(left(trim(var2))) ;

b = int(a/2);

c = int(length(byline)/2);

substr(byline,(c-b)-7,a+10) = 'VAR1=' || left(trim(var1)) || ' ' || VAR2=' || left(trim(var2));

run;

Which gives me a line that approximates the original line from the PROC PRINT, and then I can just use:

title5 #byval(byline);

in the PROC REPORT.

One more quick question (something that just came up) - is there a way of printing the titles only once when the PROC REPORT has a BY value (see my source code at the top). My client would prefer the TITLES only at the top of the report and only the BYLINE (title5, above) between each "page" of the report. I don't think this can be done without the title text values being macro variables and changing them to blanks after the first page is printed - which is just too complicated right now.

Thanks to all in advance.

Re: Proc Report Byline Problem

data_null__ — Thu, 14 Jul 2011 16:25:42 GMT

OS2Rules wrote:

Data_null_;

I can change the code in test to point to the newer tagset, but I can't do that when the job runs in our production environment.

Just include the tagset in your source program (SAS) the tagset is just a bunch of PROC TEMPLATE code. It will become part of YOUR program code. No need to bother those in IT who don't know SAS.

You could even modify the tagset to produce the BYLINE in a format that you want.

Proc Report Byline Problem

Cynthia_sas — Thu, 14 Jul 2011 17:27:55 GMT

Hi:

Wait -- you mean they WANT to see the row of dashes???? Wow.

The answer to your other question is that the SAS TITLE statement is a GLOBAL statement and your only opportunity to "turn it off" is between procedures. You will not really be able to do what you want to do with just a TITLE statement. However, TAGSETS.EXCELXP has ways to control and set the Excel print headers and print footers which CAN be separate from your SAS titles. If you use the doc='Help' sub-option with TAGSETS.EXCELXP, you will find the several sub-options for dealing with print headers/footers. Also, ODS TEXT= allows you to put information into your output -- at the beginning or end of each procedure -- and the text is separate from the title/footnote text -- but I sense this is probably not what you want to do since you want to use BY group processing and have the moral equivalent of the "old-fashioned" BYLINE.

The other alternative is to save your individual BY groups from PROC REPORT in an ODS DOCUMENT store and then use the "OB" Title statements in PROC DOCUMENT to change the titles accordingly before you do a replay to TAGSETS.EXCELXP.

Or, you may find it easier to go with the macro approach, where you invoke your PROC REPORT one time for each BY group, which will give you the chance to change titles between by groups as you envision.

This paper also has some excellent examples of using sub-options. You may eventually have to create a separate macro call for each BY group to simulate BY group processing, but I think you should explore these options first.

http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia

Proc Report Byline Problem

OS2Rules — Fri, 15 Jul 2011 17:51:39 GMT

Cynthia:

Thanks for the help. Yes - my client wants to the the row of dashes at the top of each "page" of the report. I put this in a TITLE and they seem to be satisfied that it is as close to the original as possible.

I've managed to, shall we say "persuade" them to change the format a tiny bit - which has only made the reporting more complicated.

The current report has 2 BY variables, call them VAR1 and VAR2.

The client now wants each VAR1 on a different tab of the spreadhseet, and within each tab - the report split by VAR2. (There are only 3 distinct values for VAR1, but VAR2 could have lots more.)

Is this even possible? The ExcelXP tagset lets me change tabs with "sheet_interval", but can I use only 1 of the variables from the by group? Then how do I split the report withinin the tab by the second BY var?

My brain is starting to hurt.

Thanks (again) in advance.....

Proc Report Byline Problem

OS2Rules — Fri, 15 Jul 2011 19:13:13 GMT

More....

Turns out that - yes you can do this, but it is messy. Thanks to Cynthia and her hyperlinks to the NESUG paper, I figured out how to create individual tabs within the sheet using discrete PROC REPORT statements (I usually use BY groups to create tabs). Then all I needed was to wrap the PROC REPORT in a MACRO and execute once for each value of my VAR1 - and have a BY VAR2 in the PROC REPORT.

I think this gives me just about everything that I need.

Thanks to all.

Proc Report Byline Problem

Tom — Fri, 15 Jul 2011 21:40:11 GMT

Did you try using a COMPUTE block in PROC REPORT to output the "BY" line when the value of VAR2 changes?