The SAS Output Delivery System and reporting techniques

ODS excel flyover changing default size?

Reply
Frequent Contributor
Posts: 90

ODS excel flyover changing default size?

[ Edited ]

Hello all,

A coworker that is not on "communities" is looking for some more advanced settings on the "flyover" option with ODS and excel file output. 

He has a base ODS sas code line of:

define grad_or_retain_in_prog_yr1         /analysis sum     'NO. of Students'       style(header)={tagattr="format:@" verticalalign=t flyover='A student is considered Retained or Graduated in Program if their first declared primary program Academic Interest (AI) or Major remains unchanged from their first semester of enrollment at ZZZ.'} format=blank.;


This above code works for him perfectly, save he wants the flyover to be "larger in display size".  Meaning his text is cut off and needs more real estate. I found a PDF referance from a SAS confrence for PDFs but it did not work to add "CELLWIDTH=6IN JUST=CENTER FONT_SIZE=16PT FONT_FACE=Helvetica"  no quotes as in the source I used below.  Can someone help me with the "reference" to all options having to do with “flyover”?   When we used the quoted information it changed the size of the column not the flyover.

ods pdf notoc startpage=never file="&FolderForPDF.\TitleWithFlyoverText.pdf";

ods pdf text="^S={FLYOVER='This is the CLASS data set from the SASHELP data library'

CELLWIDTH=6IN JUST=CENTER FONT_SIZE=16PT FONT_FACE=Helvetica}Students";

proc print data=sashelp.class; run;

ods pdf close;

 

Above code is from: http://www2.sas.com/proceedings/sugi31/090-31.pdf

PS We put the borrowed code in the same relative location before the closing } and after the closing quote of the flyover string.   If that is the wrong location can someone show an example where it should be for this ODS excel and flyover options?
In the attached pic my red arrow tip is in the flyover footprint and I need more area for my txt.


flyover.jpg
Super User
Posts: 11,343

Re: ODS excel flyover changing default size?

Posted in reply to kjohnsonm

The tagattr in the "works for him" code really looks like the output is being sent to Excel. So I am a bit confused about the PDF question when you are asking about Excel? Are you creating a PDF from Excel? If so why? Send the output to ODS PDF and work from their.

The define block would allow setting font size and such.

 

Since there are lots of interactions possible between the proc style options, column statement options and actual data then it would help to provide example data (instruction here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to create data step code we can use, paste into code box using {i} icon) and the entire code including current ODS destination options.

Frequent Contributor
Posts: 90

Re: ODS excel flyover changing default size?

[ Edited ]

To be clear:

I am not asking about PDF, I am saying we borrowed the code from the PDF site thinking it might work for both...   What we want is a more detailed "referance site" to the flyover option for excel file output.

Super User
Posts: 11,343

Re: ODS excel flyover changing default size?

Posted in reply to kjohnsonm

PDF is not Excel, so the options would be different. I still say that we need to see the options you are setting in your ods output and the Proc Report code.

The generic approach is going to be style settings but since I don't see where you are creating flyover text in the proc report code it's a bit difficult to say where.

And a few rows of example data will help to provide a tested solution.

Frequent Contributor
Posts: 90

Re: ODS excel flyover changing default size?

his code line:
define grad_or_retain_in_prog_yr1 /analysis sum 'NO. of Students' style(header)={tagattr="format:@" verticalalign=t flyover='A student is considered Retained or Graduated in Program if their first declared primary program Academic Interest (AI) or Major remains unchanged from their first semester of enrollment at ZZZ.'} format=blank.;
Frequent Contributor
Posts: 90

Re: ODS excel flyover changing default size?

Posted in reply to kjohnsonm

 

1) We cannot share the source data.

2) We can read and are only asking for a better documentation site to use as a referance.  

"What we want is a more detailed "referance site" to the flyover option for excel file output."

does that not exist?

Super User
Posts: 11,343

Re: ODS excel flyover changing default size?

Posted in reply to kjohnsonm

kjohnsonm wrote:

 

1) We cannot share the source data.

2) We can read and are only asking for a better documentation site to use as a referance.  

"What we want is a more detailed "referance site" to the flyover option for excel file output."

does that not exist?


1) dummy data to show the issue is all that is needed

2) Since the ODS destination is needed to know any of the options that may be available (there are at least 3 different ways that I know of to get formatted output into Excel) the ODS information is needed as minimum starting point. Also the version of SAS may be of importance as appearance in options in SAS 9.4 may not work if you are using 9.2 or 9.3.

Frequent Contributor
Posts: 90

Re: ODS excel flyover changing default size?

[ Edited ]

I need/want to see the full string:

"A student is retained in College if they switch from their first declared AIs or Major to another AI or Major within the College"

...in my flyover.

 

Given this code, save program as any name, this works on windows7 PC, program saved on network share, SAS9.4 M4.  it has minor sample data, open xls to see flyover.

 

data word_cleanup;
	input in2 3. out2 2.;
	datalines;
111 1
222 2
333 3
run;
title1 ; title2 ; title3 ; title4 ; title5 ; title6 ; title7 ; title8 ; title9 ; title10 ; 
footnote1 ; footnote2 ; footnote3 ; footnote4 ; footnote5 ; footnote6 ; footnote7 ;  footnote8 ;footnote9 ; footnote10 ;
options mlogic mprint merror symbolgen ;

* --- initialize variables ---;
%Let FName = %SysGet( SAS_EXECFILEPATH ) ;
%let sasfile= %sysget(SAS_EXECFILEname);

%let odsfile=%qsubstr(&sasfile,1, %length(&sasfile)-4);
%Let PName = %qsubstr(%sysget(SAS_EXECFILEPATH),1, %length(%sysget(SAS_EXECFILEPATH))-%length(%sysget(SAS_EXECFILEname))) ;

%macro fdate(fmt);
   %global fdate;
   %global tdate;
   data _null_;
      call symput("fdate",left(put("&sysdate9"d,&fmt)));
      call symput("tdate",left(put(today(),&fmt)));
   run;
%mend fdate;
%fdate(date9.) ;

%let outputfile=&odsfile.&tdate..xls; 
%let start_term_code=20013;
%let last_term_code=20143;
%let start_strm=2127;
%let degree_start_strm=2123;
%let latest_xw_strm=2153;
%let highlight=cxf2dcdb;
%let my_sheet=testing1;

* --- output excel sheet for each program (retention plan descr) ---;
%let outputfile=&odsfile._&tdate..xls; 
%put &outputfile;

ods Tagsets.ExcelXP 
    path = "&pname."
    file = "&outputfile."
	options (
        Embedded_Titles='Yes'
		Embedded_Footnotes='Yes'
		zoom='100'
	 );

ods Tagsets.ExcelXP 
options (
    Sheet_Name="&my_sheet."
	Sheet_Interval='none'
	Absolute_Column_Width='7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7'
	Autofit_height='yes'
	skip_space='1,0,0,1,1'
) ;
proc report data=word_cleanup nowd missing split='\' nocenter;
column in2 out2;
define in2	/ group	'NO. of Students IN2' 	style(header)={tagattr="format:@" verticalalign=t flyover = 'A student is retained in College if they switch from their first declared AIs or Major to another AI or Major within the College'} ;
define out2	/ group	'NO. of Students OUT2' 	style(header)={tagattr="format:@" verticalalign=t flyover = 'A student is retained in College if they switch from their first declared AIs or Major to another AI or Major within the College'} ;
RUN;
ods tagsets.Excelxp close ;

This is not the full program, just enuff to create a xls file with a few rows and a fly over...   I do not want to change my ods option:

Absolute_Column_Width='7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7' to resolve this.

 

-thank you  -KJ

SAS Super FREQ
Posts: 8,868

Re: ODS excel flyover changing default size?

Posted in reply to kjohnsonm

Hi,

  I can make FLYOVER work in a data cell with TAGSETS.EXCELXP, but the FLYOVER does not work for the TITLE, so I would not expect it to work for ODS TEXT. I'd suggest working with Tech Support if you have a lot of questions about FLYOVER.

 

cynthia

flyover.png

Frequent Contributor
Posts: 90

Re: ODS excel flyover changing default size?

[ Edited ]
Posted in reply to Cynthia_sas

The origin of our quest from a lady SAS programer here where I work shared this code by email:

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

  title; footnote;

  ods listing close;

  ods tagsets.excelxp file='c:\temp\flyover.xls' style=statistical;

  proc print data=prdsale noobs label split='*';
     where country eq 'CANADA' and year eq 1993;
     id country region division;
     var prodtype product quarter month year;
     sum predict / style(header)={tagattr='format:Currency' flyover='Predicted Total'};
     sum actual / style(column)={tagattr='format:Currency' flyover='Actual Total'};
     label prodtype = 'Product*Type'
           predict  = 'Predicted*Sales'
           actual   = 'Actual*Sales';
  run; quit;

  ods tagsets.excelxp close;

  ods listing;

and it fully works for me...?  how about you??

PS I wish I had this code when I frist started this quest.   I could have had a working base sample to kick this party off... 8(

SAS Super FREQ
Posts: 8,868

Re: ODS excel flyover changing default size?

Posted in reply to kjohnsonm
Hi, My screen shot shows that Flyover works in a data cell using TAGSETS.EXCELXP. It does not work in a TITLE for me. Therefore, I would not expect it to work with ODS TEXT.
cynthia
Super User
Posts: 11,343

Re: ODS excel flyover changing default size?

Posted in reply to kjohnsonm

Minor suggestion that may save occasional typing with Title and footnote statements. When you use Title; or Title1; it clears all of the title statements with higher numbers. So instead of

title1 ; title2 ; title3 ; title4 ; title5 ; title6 ; title7 ; title8 ; title9 ; title10 ;

 

Title1; accomplishes the same. Footnote works similarly.

And if you only want to clear title3 and higher: Title3;

Frequent Contributor
Posts: 90

Re: ODS excel flyover changing default size?

[ Edited ]

thanks, news to me... We all have keyboard macros pre-loaded when they start here from long ago... I can test it out and trim it down thanks agian.

SAS Super FREQ
Posts: 304

Re: ODS excel flyover changing default size?

Posted in reply to kjohnsonm

Does this code reproduce the problem?

 

ods _all_ close;

ods tagsets.ExcelXP file='C:\temp\temp.xml' style=HTMLBlue;
  proc report data=sashelp.class nowd;
  column name sex age height weight;
  define height / analysis sum 'NO. of Students' 
    style(header)=[tagattr="format:@" verticalalign=t flyover='A student is considered Retained or Graduated in Program if their first declared primary program Academic Interest (AI) or Major remains unchanged from their first semester of enrollment at ZZZ.'];
  run; quit;
ods tagsets.ExcelXP close;

 

I don't think there is any way to programmatically change the size of the comment box except by using an Excel macro.

 

To manually resize the box, right-click on the cell with the comment, choose Edit Comment from the pop-up menu, resize the box, and then click another cell.  The comment box stays resized as long as the workbook is open, but the new size is not persisted.

 

Vince DelGobbo

SAS R&D

Ask a Question
Discussion stats
  • 13 replies
  • 855 views
  • 3 likes
  • 4 in conversation