/*Producing customized reports using SAS DDE (Dynamic Data Exchange), MS-WORD, and MS-EXCELL
by Kaz Uekawa, Ph.D (kuekawa@alumni.uchicago.edu)
See www.estat.us for other SAS programs
September 2004

This program is not without a problem.  I summarized problem at the bottom of this program.
Please email me about your solution
kuekawa@alumni.uchicago.edu


TO USE THIS PROGRAM, put
reportProduce.doc
reportProduce.xls

in C:\temp

Or modify the path name to whatever you like in the following program.
*/



options noxwait noxsync; 
filename cmds dde 'WinWord|System'; 


/*Creating a report for people whose height and weight information is 
in the data set sashelp.Class*/



/*Reading a permanent data and creating a temporary data*/
data ABC;
set sashelp.Class; 
****************;
*Creating a character variable indicating a person's BMI status (Body Mass Index);
weight_metric=weight*0.45359237;
height_metric=(height* 2.54)/100 ;
BMI=weight_metric/(height_metric**2);
/*Definition of obesity Normal weight = 18.5-24.9 
Overweight = 25-29.9  Obesity = BMI of 30 or greater */
length status $ 15;
If BMI < 18.5 then status="Your BMI status is Underweight";
If BMI => 18.5 and BMI < 25  then status="Your BMI status is  Normal";
If BMI => 25 and BMI < 30 then status="Your BMI status is  Overweight";
If BMI >= 30 then status="Your BMI status is  obese ";
****************;
run;

/*creating group mean scores*/
proc means data=ABC;
var weight_metric height_metric BMI;
ods output summary=ABC_grandmean;
run;


%macro whatever (name=);
/*Open a word document.  The software MS-WORD itself has to be open already, 
but the document itself must be closed*/
data _null_; 
file cmds; 
put '[FileOpen .Name = "' "C:\temp\reportProduce.doc" '"]'; 
put '[update1]'; 
run; 

/*Excel sheet reportProduce.xls must be open already*/
/*(1) populating excel sheet with individual values*/
FILENAME ddedata DDE
"excel|C:\temp\[reportProduce.xls]Sheet1!r1c1:r1c4" notab;
FILENAME ddecmds DDE "excel|system";
data _null_;
set ABC;
where name="&name";
file ddedata;
put name '09'x  weight_metric '09'x  height_metric '09'x  BMI;
run;

/*(2) populating excel sheet with group mean)*/
FILENAME ddedata DDE
"excel|C:\temp\[reportProduce.xls]Sheet1!r2c2:r2c4" notab;
FILENAME ddecmds DDE "excel|system";
data _null_;
set ABC_grandmean;
file ddedata;
put weight_metric_mean '09'x  height_metric_mean '09'x  BMI_mean ;
run;


/*Next, populating a word document directly through bookmarks*/
/*I inserted a bookmark "person" in the word document reportProduce.doc*/
filename testit dde 'winword|"c:\temp\reportProduce.doc" !name' notab;
data _null_;
set ABC;
file testit;
where name="&name";
put name;
run;


options noxwait noxsync; 
filename cmds dde 'WinWord|System'; 
data _null_; 
file cmds; 
put '[FileSave]'; 
put '[FileClose 2]'; 
run; 


x "copy C:\temp\reportProduce.doc C:\temp\report_for_&name..doc";

%mend whatever;
%whatever (name=Carol);

/*Stop here before you run this for the other person in the data, say, Mary*/
/* Right now if you run this program without stopping here, it will create lots documents for everybody
in the data, like Mary and John.

But I am encountering a problem.

In my other computer, each time it tries to run this program, I get a pop-up message about updating, so 
I have to click YES.  So if I have many person's reports, I have to sit in front of the computer all day.

In the laptop computer I am using to write this, I have no problem of this, but when I open the documents,
they automatically update themselves, which is bad.  Basically all my reports will have the same
graphs, nullifying all my efforts.  This is because these documents update themselves to
whatever the current excel sheet says (which is the last person's in the data).

I tried Visual basic solution, so somehow updating thing is turned off and on through SAS.  I failed and
it is a long story.  Basically people helped me write a VB macro within the template word doc, so 
SAS DDE can send a message to turn on and off UPDATE thing.  They all failed.  Fail fail fail.


Despite the potential that I can produce reports to hundreds of reports overnight, I am not being able
to achieve that because either:
a) each iteration of macro I have to say YES to a pop-up window about UPDATING.
b) when I open the result documents, they update themselves without asking me.


Can someone come up with a solution, a real solution with an actual result????
Like real results using these templates that I wrote, including this syntax?

I have tried people's advises only to waste time and I am now tired of trying.
People can help me to the point where I am almost there, but not quite.

Please email me your solution at kuekawa@alumni.uchicago.edu
*/

/*Right now you can do this one by one.  Maybe at each opening of the result doc you can 
cut the links manually, so they won't get affected.  But in some configuration of MS-WORD,
it asks you if you want to update, so you say NO.  The latter is a better scenerio.*/

%whatever (name=Mary);
%whatever (name=Alfred);
%whatever (name=Alice);
%whatever (name=Barbara);
%whatever (name=Henry);
%whatever (name=James);
%whatever (name=Jane);
%whatever (name=Janet);
%whatever (name=Jeffrey);
%whatever (name=John);
%whatever (name=Joyce);
%whatever (name=Judy);
%whatever (name=Louise);
%whatever (name=Mary);
%whatever (name=Philip);
%whatever (name=Robert);
%whatever (name=Ronald);
%whatever (name=Thomas);
%whatever (name=William);