topic Re: Pass values to filename and title. in ODS and Base Reporting

Pass values to filename and title.

around52 — Fri, 03 Feb 2012 20:34:17 GMT

Hi,

I am using ODS to output the results of PROC REPORT into XLS files, and I have run into a few issues. To give you a little background, I am using a macro loop to produce reports for groups of people within an organization. I want to include the group's formal name in the report title (have had some success with this), and I want to include the group's abbreviated title in the exported filename (have had no success with this). It might be helpful to add that the BY STATEMENT is only included in the PROC REPORT so that #BYVAL can be used in TITLE2. The BY STATEMENT is otherwise unnecessary because each iteration of the loop runs the report for a different group.

1. I would like to pass the value of a variable (group_desc) to a title. I was able to do this using #BYVAL. The problem is that my output provides an unwanted line of text, which shows the value produced by the BY STATEMENT in PROC REPORT. Since my TITLE already contains the relevant value, I would like to suppress or turn off this unwanted line of text.

2. I would like to pass the value of a different variable (acad_group) to the filename. I have not yet figured out how to do this.

Here is an abbreviated version of the relevant parts of my code. Thanks in advance for your help!

ods html file="c:\temp\college_x_term_&I..xls" style=Styles.Custom /*I want to include the value for acad_group in the filename*/

headtext="<style> td {mso-number-format:\@}</style>";

title1 bold color=CX000000 bcolor=CXFFFFFF &titleterm ;

title2 bold color=CX000000 bcolor=CXFFFFFF 'Grade Distribution for #BYVAL(group_desc)'; /*Populated with the by statement (below)*/

proc report data=work.dummy_report nowindows headline;

by group_desc; /*The by statement is included to populate the #BYVAL option in TITLE2 (above)*/

column acad_group group_desc A B C D E F;

define acad_group / noprint group;

define group_desc / "College" group;

define acad_org / "Department" group;

define A / analysis "N" f=comma6.0;

define B / analysis "N" f=comma6.0;

define C / analysis "N" f=comma6.0;

define D / analysis "N" f=comma6.0;

define F / analysis "N" f=comma6.0;

run;

ods html close;

quit;

Re: Pass values to filename and title.

Cynthia_sas — Fri, 03 Feb 2012 21:10:47 GMT

Hi, when you are using the #BYVAL, #BYLINE special strings in your title, you probably also want to turn off the normal BYLINE behavior with

options nobyline;

before your code and

options byline;

after all your code.

Without seeing your macro code, it is hard to make any recommendations about how to get the value of ACAD_GROUP into a macro variable. You said that you are using a macro loop, so it is reasonable to think that you could use PROC SQL inside your macro loop to get the ACAD_GROUP that corresponds to your BY group and make a macro variable using INTO. Or, worst case, you could hard code it into the macro loop.

But the example below illustrates, not with a macro loop, but with 2 calls to a pre-defined macro, how to make a macro variable that can be used in a subsequent file = option.

cynthia

%macro doclass(want=);
proc sql noprint;
    select sex into :ag
    from sashelp.class
    where name = "&want";
quit;

%let ag = &ag;
%put value of ag is &ag;

title "Name is &want gender is &ag";
title2 "File name is c:\temp\myfile_&ag..html";
ods listing close;
ods html file="c:\temp\myfile_&ag..html";

   proc report data=sashelp.class nowd;
     column name age sex height weight;
     where name = "&want";
   run;
   ods html close;
   title;
%mend doclass;

options mprint;
%doclass(want=Alice)

%doclass(want=Alfred)

Re: Pass values to filename and title.

around52 — Sat, 04 Feb 2012 20:12:55 GMT

Thank you very much Cynthia. Your suggestions solved both of my problems!

1. The "options nobyline" statement took care of the text that I wanted to remove from my output.

2. To pass the value of the acad_group variable to the output filename. I inserted this code into my macro loop.

PROC SQL NOPRINT;

SELECT ACAD_GROUP INTO :AG

FROM TEMP_GDR&I;

QUIT;

%LET AG=&AG;

%PUT VALUE OF AG IS &AG;

and then called &AG in my file name.

ods html file="c:\temp\college_x_term_&AG..xls" style=Styles.Custom

headtext="<style> td {mso-number-format:\@}</style>";