topic Re: DDE Footnotes in Excel in Microsoft Integration with SAS

DDE Footnotes in Excel

bspangler — Mon, 03 Feb 2014 22:56:15 GMT

Hi,

but everytime I do, I get errors regarding too many parentheses and/or quotation marks. This is a macro to print a title in MS Excel and also to print footers left alligned stacked on top of each other. Sometimes I will have 3 lines, and sometimes I will have four lines in the footer. Below is an example of what I am trying to achieve. This code was handed down to me, so any input is great.

Any ideas? Thanks!

What I want:

Source: Membership File

Calculated as of: December 1, 2013

Confidential && Proprietary

What I am currently getting:

Source: Membership File

Calculated as of: December 1, 2013

Confidential && Proprietary

%macro footer(sheet,left1,left2);

filename DDEcmds dde 'excel|system';

data _null_;

file DDECmds;

put%unquote(%bquote('[workbook.activate("&sheet")]'));

put %unquote(%str(%'[page.setup(%nrbquote("&C&B&16&""Times New Roman""&title.%'%'0d%'x%'&12&A"))]%'));

%if &left2 ne %then %do;

put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""TimesNew Roman""&left1.%'%'0d%'x %'&left2.%'

%'0d%'x %'&10&L&""Times New Roman""Confidential %' %'26%'x %'26%'x %' Proprietary"))]%'));

%end;

%else

%do;

put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times
New Roman""&left1.%'%'0d%'x %'%str(Copyright 2014 LLC. All rights reserved.)%'%'0d%'x
%'&10&R&""Times New
Roman""&P+&page.%'%'0d%'x %'&10&L&""Times
New Roman""Confidential %' %'26%'x %'26%'x %' Proprietary"))]%'));

%end;

run;

%mend footer;

%let page=0;

%let title=Membership Profile;

%let agedate=%str(December 1, 2013);

options mprint;

%footer(Marital Status,Source: Membership File);

%footer(Reported Age,Source: Membership File,%str(Calculated as of: &agedate));

Re: DDE Footnotes in Excel

Peter_C — Thu, 06 Feb 2014 15:51:37 GMT

trying to make your code work, I focus on this page.setup line.

put %unquote(%str(%'[page.setup(%nrbquote("&C&B&16&""Times New Roman""&title.%'%'0d%'x%'&12&A"))]%'));

to test it on its own, I have adapted it slightly and can persuade it to generate the following PUT statement. Is it correct?

put '[page.setup("&C&B&16&""Times New Roman""&title.''0d'x% '&12&A")]';

At that stage, did you want &C&B to resolve macro variables or appear as text passed into excel?

Was some &title to be resolved or passed to excel as that text?

For my testing, I used the code :

%put %quote( put %unquote(%str(%'[page.setup(%nrbquote("%nrstr(&C&B&16&)""Times New Roman""%nrstr(&title.)%'%'0d%'x% '%nrstr(&12&A)"))]%')); );

Around "syntax being tested", this provides the wrapper %put %quote( syntax being tested ; ) ;

To avoid attempting to resolve the macro variables I referred above, I wrapped those references in %nrstr().

Re: DDE Footnotes in Excel

bspangler — Mon, 10 Feb 2014 18:43:51 GMT

Hi, thank you so much for your response! I want the &C&B to make the title centered and bold, and I want &title to resolve to "Membership Profile." So in the end, I will end up with an Excel workbook page with a header that says "Membership Profile" at the top center of the page in bold. Also, I tried using the code you wrote above and although I didn't get any errors, it did not add the title to the Excel workbook page.

Re: DDE Footnotes in Excel

Peter_C — Mon, 10 Feb 2014 19:10:34 GMT

Thank you for this small part of the full explanation needed.

If you indicate what you wanted to generate from each piece if the nacro being executed, it would make it possible for us to help.

Re: DDE Footnotes in Excel

bspangler — Mon, 10 Feb 2014 20:09:34 GMT

Hi, the title resolves fine via the %let statement, and the &C&B resolve via the page.setup for DDE. It is really the footnotes that I am having a problem with. They all output to Excel, but I just want to switch the order of the Confidential and Proprietary line with the Copyright line. I believe I defined all the macros in the original post, let me know if I can do anything else to help explain. Thanks!

%if &left2 ne %then %do;

put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""TimesNew

Roman""&left1.%'%'0d%'x %'&left2.%'

%'0d%'x %'&10&L&""Times New Roman""Confidential %' %'26%'x %'26%'x%'Proprietary"))]%'));

%end;

Re: DDE Footnotes in Excel

bspangler — Mon, 10 Feb 2014 22:27:33 GMT

Hi!

Good news, I was able to get the footnote lines switched, so now all I need to do is get all the embedded footnote lines to be left alligned and in Times New Roman font. When there are only 3 lines in the embedded footnote, the 'Copyright' line is not left alligned and is not in Times New Roman, and when there are 4 lines in the embedded footnote, the 'Copyright' line is left alligned, but not in Times New Roman, and the 'Confidential and Proprietary Line' is not left alligned. Any help is appreciated, thanks!

%macro footer(sheet,left1,left2);

filename DDEcmds dde 'excel|system';

data _null_;

file DDECmds;

put %unquote(%bquote('[workbook.activate("&sheet")]'));

put %unquote(%str(%'[page.setup(%nrbquote("&C&B&16&""Times New Roman""&title.%'%'0d%'x %'&12&A"))]%'));

%if &left2 ne %then %do; put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times New Roman""&left1.%'%'0d%'x%'&left2.%'%'0d%'x%'Confidential and Proprietary%'%'0d%'x %'&10&R&""Times
New Roman""&P+&page.%'%'0d%'x %'&10&L& %str(Copyright 2014 LLC. All rights reserved.)"))]%'));%end;

%else %do; put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times New Roman""&left1.%'%'0d%'x%'Confidential and Proprietary%'%'0d%'x%'&10&R&""Times New Roman""&P+&page.%'%'0d%'x%'&10&L& %str(Copyright 2014 LLC. All rights reserved.)"))]%'));%end; run;

%mend footer;

%let page=0;

%let title=Membership Profile;

%let agedate=%str(December 1, 2013);

options mprint;

%footer(Marital Status,Source: Membership File);

%footer(Reported Age,Source: Membership File,%str(Calculated as of: &agedate));

Re: DDE Footnotes in Excel

bspangler — Mon, 10 Feb 2014 23:20:49 GMT

Thank you for your help - I was able to work it all out!

%if &left2 ne %then %do;

put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times New Roman""&left1.%'

%'0d%'x %'&left2.%'

%'0d%'x
%'Confidential and Proprietary%'

%'0d%'x %'&10&R&""Times New Roman""&P+&page.%'

%'0d%'x %'

%end;

%else %do;

put %unquote(%str(%'[page.setup(,%nrbquote("&10&L&""Times New Roman""&left1.%'

%'0d%'x
%'Confidential and Proprietary%'

%'0d%'x
%'&10&R&""Times New Roman""&P+&page.%'

%end;