/*** TIP00397 ***/

/* From Nitin Chandak (01/Dec/2003)
Email: chandaknitin at yahoo  dot  com
Insert SAS/Graph in Excel

1.      First create excel template (get it here - tip00397.xls and rename it to tmp.xls, 
          it has an excel macro embedded within it)
2.      Create one macro for importing SAS graphs.

Macro Code: -

Public Sub InsertGraph()
                Dim imgpath As String
                imgpath = Range("A65536")
                ActiveSheet.Pictures.Insert(imgpath).Select
End Sub
3.      Generate graph in SAS.
4.      Remember the path where gif file is created. Copy that path to Excel in cell no "A65536".
5.      Rest of the work Excel will do.

Change the path wherever required in the below code.
*/


proc format;
value sex
0='Females'
1='Males'
;
run;

data lung;
input patient age sex height tlc;
format sex sex.;
label
patient='Patient ID number'
age='Age in years'
sex='Sex (0=female 1=male)'
height='Height (cm)'
tlc='Total lung capacity'
;
cards;
  1   35   0  149  3.40
  2   11   0  138  3.41
  3   12   1  148  3.80
  4   16   0  156  3.90
  5   32   0  152  4.00
  6   16   0  157  4.10
  7   14   0  165  4.46
  8   16   1  152  4.55
  9   35   0  177  4.83
 10   33   0  158  5.10
 11   40   0  166  5.44
 12   28   0  165  5.50
 13   23   0  160  5.73
 14   52   1  178  5.77
 15   46   0  169  5.80
 16   29   1  173  6.00
 17   30   0  172  6.30
 18   21   0  163  6.55
 19   21   0  164  6.60
 20   20   1  189  6.62
 21   34   1  182  6.89
 22   43   1  184  6.90
 23   35   1  174  7.00
 24   39   1  177  7.20
 25   43   1  183  7.30
 26   37   1  175  7.65
 27   32   1  173  7.80
 28   24   1  173  7.90
 29   20   0  162  8.05
 30   25   1  180  8.10
 31   22   1  173  8.70
 32   25   1  171  9.45
;
run;

%let graphout = "C:\tmp\mygraph.gif"; /*Set path for graph*/

goptions reset = all;
goptions  ftext = swiss htitle = 5 htext = 3 gunit = pct
border cback = white hsize = 4in vsize = 4in;
filename outgraph &graphout;
*goptions gsfname = outgraph dev = gif373 ;
/* The size is about 3.93in by 2.95in. */
goptions gsfname = outgraph dev = gif570;
/* The size is about 6in by 4.5in. */
title "Total lung capacity vs height";
title2;
axis1 order = (-10 to 25 by 5) offset = (3,0) label = (a=90 'My dev') minor=none;
axis2 order = (-12 to 52 by 10) offset = (5,5) label=('My X') minor = none;
symbol1 i = join c = black l=5 v = plus ;
symbol2 i = join c = red l= 3 w = 5;
symbol3 i = join c = blue ;
legend label=none value=(h=2 font=swiss 'group1' 'group2' 'group3')
       position=(top right inside) mode=share cborder=black;

proc gplot data = lung;
  plot tlc*height;
run;
quit;

OPTIONS noXWAIT noXSYNC;
X "copy C:\tmp\tmp.xls C:\tmp\tmp_&sysdate..xls" ; /*Copy template file*/
X "'C:\Program Files\Microsoft Office\Office\EXCEL.EXE' C:\tmp\tmp_&sysdate..xls"; /*Open new excel file*/

DATA _NULL_;
     rc = SLEEP(15);
RUN;

proc sql noprint;
select name into :columns1 SEPARATED by ' ' from sashelp.vcolumn
where libname='WORK'
and memname = 'LUNG';

FILENAME ddedata DDE "excel|sheet1!R2C1:R2C10" ; /*Insert headings*/
data _null_;
set work.lung(obs=1);
FILE ddedata;
put "PatientID" '09'x 'Age' '09'x 'Sex' '09'x 'Height(cm)' '09'x 'TotalLungCapacity';
run;

FILENAME ddedata DDE "excel|sheet1!R3C1:R100C10" ;
data _null_;
set work.lung;
FILE ddedata;
put &columns1;
run;

FILENAME ddedata DDE "excel|sheet1!r65536c1:r65536c1" ;

data _null_;
set work.lung(obs=1);
FILE ddedata;
put &graphout; /*Writing file path to A65536 in Excel Cell*/
run;

FILENAME ddecmds DDE "excel|system";

DATA _NULL_;
     FILE ddecmds;
PUT '[select("r3c6")]';
put '[run("InsertGraph")]';
/*
                        Public Sub InsertGraph()
                        Dim imgpath As String
                        imgpath = Range("A65536")
                            ActiveSheet.Pictures.Insert(imgpath).Select
                        End Sub
*/
PUT '[save()]';
PUT '[quit()]';
RUN;

/*** end of tip 00397 ***/