BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9
Hello,
I want the output in my Excel and PDF to look like this -

title1 |------|
title2 |IMG|
title3 |------|

I looked up the forum and SAS Notes but the posts seem to be close but not exactly what I want.
Sample 41703: How to wrap text to the right of an image in a title or footnote in an ODS PDF file
Usage Note 24229: Can I get images in my titles with ODS PRINTER?

This thread comes close -
http://support.sas.com/forums/thread.jspa?messageID=44150걶

But I want this trick to work to both in Excel and PDF and I am not sure how to design the Title statement for this. The code (we are running Stored Processes) is here -

%macro header;

%IF &format = Excel %THEN %DO; %LET _ODSDEST =tagsets.excelxp ; %LET _ODSSTYLE =; %LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS =options(
embedded_titles="yes"
autofilter="3-46"
frozen_headers="13"
/*frozen_rowheaders="2"*/
absolute_column_width="8.3,11,7,9,8,8"
autofit_height="yes"
/*autofit_width="no"*/
sheet_label="test"
/*wraptext="yes"*/
/*(mergecell="yes"*/
);
RUN;
DATA _NULL_; rc = STPSRV_HEADER('Content-type', 'application/vnd.ms-excel');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=tmp.xls');
RUN; %END;


%ELSE %IF &format = PDF %THEN %DO; %LET _ODSDEST = pdf; %LET _ODSSTYLE = styles.pdftemp; %LET _ODSSTYLESHEET = ; %LET _ODSOPTIONS = BOOKMARKGEN=NO COMPRESS=9 STARTPAGE=NO; DATA _NULL_; rc = STPSRV_HEADER(
'Content-type', 'application/pdf');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=Test_op_prgm_smry.pdf'); RUN; %END ;

%ELSE %IF &format = %THEN %DO; %LET _ODSDEST = ; %LET _ODSSTYLE = ; %LET _ODSSTYLESHEET = ; %LET _ODSOPTIONS = ; DATA _NULL_; rc = STPSRV_HEADER('Content-type', 'application/vnd.ms-excel');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=Test_op_prgm_smry.xls'); RUN; %END;

%ELSE %IF &format = HTML %THEN %DO; RUN; %END;
%mend;
%header;

ods escapechar='^';
title j=left HEIGHT=3 COLOR=BLACK FONT=verdana 'Online Summary' j=c "^S={preimage='/sasdev/../logo.bmp'}";
title2;*not including the entire line of code;
title3;

If I can get the trick for PDF alone, that also works for me.

Thanks,
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
Hi:

I am not sure that you can get the same code working for both Excel and PDF -- they are 2 fundamentally different destinations.

Plus, TAGSETS.EXCELXP creates Excel 2003 XML, which by Microsoft design, does not allow the use of images. For example, if you try to use GCHART with TAGSETS.EXCELXP, you see this:
[pre]
390 ods tagsets.excelxp file='c:\temp\someimage.xls' style=sasweb;
NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\someimage.xls
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 ods statement for more information.
391 ods escapechar='~';
392 proc gchart data=sashelp.class;
393 vbar sex / sumvar=height type=mean;
394 run;

Excel XML does not support output from Proc:Gchart
Output will not be created.
NOTE: 2 records written to c:\temp\output\gchart.png.
395 quit;
[/pre]

It doesn't matter whether you are using PROC GCHART or PREIMAGE -- TAGSETS.EXCELXP will NOT use images. You might have to switch to an HTML-based destination in order to get images to work with Excel when the file is opened.

Again, since stored processes are involved, you may wish to work with Tech Support on this question.

cynthia
saspert
Pyrite | Level 9
Hi Cynthia,
Do you know if we can make it work for PDF atleast? I did learn from our earlier discussions that ExcelXp wont like images.

Thanks,
Cynthia_sas
SAS Super FREQ
Hi:
If that previous forum posting provides code that does what you want, then I don't know what you mean when you ask:
Do you know if we can make it work for PDF at least?

Make what work??? In your original post, you showed this:
[pre]
title1 |------|
title2 |IMG|
title3 |------|
[/pre]

I don't know what the above drawing means...you want title1 text string, then an image underneath title1 and then you want title3 to be underneath the image???

Did you try the code from the previous forum posting (at the bottom)??
[pre]
ods pdf file='c:\temp\logo_labelx.pdf';
ods escapechar='~';
title j=l '~S={preimage="c:\temp\kermit.gif"}'
j=c '~S={vjust=m} 1) Title line 1~n Another Line~n A third line';

proc report data=sashelp.shoes(obs=6)nowd;
column region product sales;
define region / 'Region';
run;

ods pdf close;
[/pre]

If so, can you explain how you would change this above output in PDF to be what you want??? If you need MORE control over image or text placement, then you may be looking at using ODS LAYOUT with ODS PDF to do what you want.

cynthia
saspert
Pyrite | Level 9
Hi,
I tried the code from the posting but it does not seem to work for me. 😞 I will try to be more simple this time.
I need 3 titles on the top left corner and the image on the top right corner.

Here is my code -

ods escapechar='^';
title1 j=l HEIGHT=3 COLOR=BLACK FONT=verdana
"^S=(vjust=l) Online Summary^n&STARTDATE to &ENDDATE^n&ReportDescription"
j=r "^S={preimage='/sasdev/.../logo.bmp'}";

Thanks
Cynthia_sas
SAS Super FREQ
Hi:
This worked for me:
[pre]
ods pdf file='c:\temp\logo_alt.pdf';
ods escapechar='~';
title j=l '~S={vjust=m} 1) Title line 1~n 2) Another Line~n 3) A third line'
j=r '~S={preimage="c:\temp\kermit.gif"}';


proc report data=sashelp.shoes(obs=6)nowd;
column region product sales;
define region / 'Region';
run;

ods pdf close;
[/pre]

But I was also pointing to a local copy of KERMIT.GIF. I notice that you are still using a physical server location for your LOGO and I don't know whether that will work. In my output, the 3 title lines are on the left and a lovely picture of Kermit is on the right and the vjust=m causes my 3 title lines to be middle-aligned with the image of Kermit. If you wanted the 3 titles to be top-aligned, then you would change the attribute to be vjust=t.

If you get the titles, but see a red X for the image, then you have image location issues with the stored process and you may want to work that issue with Tech Support.
cynthia

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
  • 5 replies
  • 1749 views
  • 0 likes
  • 2 in conversation