topic Re: Printing headers for each page in ods (excel) , inserting pdf's, and setting column height in ODS and Base Reporting

Printing headers for each page in ods (excel) , inserting pdf's, and setting column height

LB — Fri, 11 Sep 2015 16:48:58 GMT

Happy Friday!

I have a three stage question that is interrelated.

Most likely to go to the ODS excel option to export to excel I may have a data set that looks something like this:

Medical Center Facility Number_cases PDF

Some of the times there may be as few as five observations, but in other cases there could be 50-

So is there a way to

1)Set the titles,headers,footnotes so that they repeat for every page when it runs over one page when printed

2) set the column height for each observation

3) Insert pdf's through SAS or does this need to be done via vbs scripts

Thank you for your time.

Re: Printing headers for each page in ods (excel) , inserting pdf's, and setting column height

Fugue — Fri, 11 Sep 2015 19:12:02 GMT

With regard to questions 1 & 2, have you looked at ODS Excel XP options? http://support.sas.com/rnd/base/ods/odsmarkup/TipSheet_ods_xl_xp.pdf

Re: Printing headers for each page in ods (excel) , inserting pdf's, and setting column height

LB — Fri, 11 Sep 2015 21:42:30 GMT

Many Thanks!

Re: Printing headers for each page in ods (excel) , inserting pdf's, and setting column height

Cynthia_sas — Sat, 12 Sep 2015 03:39:15 GMT

Hi:
1) don't know what you mean...whether the titles repeat for every page depends on the destination. For "paged" destinations, titles, column headers and footnotes should repeat. For non-paged destinations like ODS HTML, the title will appear at the top of the HTML page, the footnote will appear after the end of the table and the column headers will appear only 1 time unless you have BY group processing.
2) Maybe -- depends on what you mean by "set the column height for each observation"
3) you could insert LINKS to a PDF file (such as on the row for Asia, you could have a link to the file ASIA.PDF and on the row for Pacific, you could have a link to the file PACIFIC.PDF.

Seeing some test data in order to understand what you mean when you say there could be 5 rows or 50 and what type of criteria you envision will determine the height of each destination.

cynthia

Re: Printing headers for each page in ods (excel) , inserting pdf's, and setting column height

LB — Tue, 15 Sep 2015 16:54:56 GMT

Cynthia-

1) I have another output document to XML/excel that it does not. I have yet to work with the ODS tipsheet to see what additional options I can play with-

But what do you mean by paged?

2) I believe the standard height size per row is 4, I think they can also be changed by what I was reading on the tip sheet.

3) I wish I could use links for this-That would be easy. Right now there is no security method or venue to place these files and they are highly sensitive documents.

Lawrence

Re: Printing headers for each page in ods (excel) , inserting pdf's, and setting column height

Cynthia_sas — Wed, 16 Sep 2015 23:09:04 GMT

Hi:

I don't know how you are planning to insert an entire PDF document into Excel. I tried and Excel will NOT insert a PDF document into the file. No matter what I try the ONLY thing I can do in Excel is to insert a LINK to a PDF file -- I cannot actually INSERT the physical PDF file into Excel.

What I mean by "paged" destinations is that RTF and PDF, for example support the use of the DATE and NUMBER options; they respect the SAS margin options. When I use a SAS title with RTF and PDF, I see a TITLE on EVERY physical page. On the other hand, when I use a TITLE with ODS HTML (which is NOT a paged destination), I see the TITLE at the top of the table and a FOOTNOTE at the bottom of the table. Consider this program:

ods pdf file='c:\temp\showtitle1.pdf';
ods rtf file='c:\temp\showtitle2.rtf';
ods html file='c:\temp\showtitle3.html';
ods tagsets.excelxp file='c:\temp\showtitle4.xml' style=htmlblue
options(embedded_titles='yes' embedded_footnotes='yes');

title 'This is my Title';
footnote 'This is my Footnote';
proc print data=sashelp.shoes(obs=100);
run;

ods _all_ close;

I see the title and footnote on EVERY page for the RTF and PDF file. But for the HTML and TAGSETS.EXCELXP file, the TITLE is at the top of the row #1 and the footnote is at the bottom under the obs #100.

See this output (shown only for PDF, HTML and XML):

You CAN change the height of the cells. How you change the height of the cells depends on the procedure you are using and whether you want to change ALL the cells or only SOME of the cells. And, then there is the issue of whether the HEIGHT style attribute will be respected by all destinations. Run this code and see which destinations use the bigger HEIGHT value for the summary line:

ods pdf file='c:\temp\showht1x.pdf';
ods rtf file='c:\temp\showht2x.rtf';
ods html file='c:\temp\showht3x.html';
ods tagsets.excelxp file='c:\temp\showht4x.xml' style=htmlblue;

title;
footnote;
proc report data=sashelp.class(obs=3)
style(summary)={cellheight=.5in vjust=m font_weight=bold color=red};
rbreak after / summarize;
run;

ods _all_ close;

But for many destinations, the height with "autoadjust" depending on the width of the cell. for example:

data newclass;
length newvar $500;
set sashelp.class(obs=3);
if _n_ = 1 then newvar = repeat(catx(' ',name,sex,'Twas brillig and the slithy toves did gyre and gimble in the wabe'),3);
else if _n_ = 2 then newvar = catx(' ',name,sex,age);
else if _n_ = 3 then newvar = catx(' ',name,sex,'abcdefg','hijklmnop','qrstuv','wxyz');
run;

ods pdf file='c:\temp\showwd1.pdf';
ods rtf file='c:\temp\showwd2.rtf';
ods html file='c:\temp\showwd3.html';
ods tagsets.excelxp file='c:\temp\showwd4.xml' style=htmlblue;

title;
footnote;
proc report data=newclass ;
define newvar / display
style(column)={width=1.5in font_weight=bold color=red};
run;

ods _all_ close;

Notice for the above code, how the row for each of the 3 observations will automatically adjust the height for the row because the width of the NEWVAR cell was fixed.

cynthia

Re: Printing headers for each page in ods (excel) , inserting pdf's, and setting column height

Cynthia_sas — Wed, 16 Sep 2015 23:10:10 GMT

Sorry, I meant to post the pix of the HTML output too:

Re: Printing headers for each page in ods (excel) , inserting pdf's, and setting column height

LB — Thu, 17 Sep 2015 19:22:55 GMT

Cynthia-

Thanks for your explanation on 'paged'.

As to excel, if you go to insert tab, then insert object you can embed a pdf.

I have some vba code that actually embeds the pdf and then renames it.

I am trying to translate that to vbs w/o much success. If i figure that out, then I will share it.

Unfortunately the Microsoft developers network is nowhere as near as helpful as the SAS community forum and in some cases seems outright nasty in some of the threads. Glad to be using SAS 98% of the time!

What I am trying to do is the holy grail to some degree but one of my colleagues needs to embed 200+ pdf's into excel documents.

Lawrence