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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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)

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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)

around52
Calcite | Level 5

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>";

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
  • 2 replies
  • 3345 views
  • 1 like
  • 2 in conversation