/**********************************************************************/
/* Set up DDE file allocation */
filename excel DDE
'Excel|
c:\data\excel\ddetests\[example1.xls]European!R6C1:R12C2';
/* Read data in from EXCEL spreadsheet */
data sales;
length city $10;
infile excel dlm='09'x notab dsd missover;
input city $ sales;
/* Print report */
proc print;
title1 'Black Tulip Records Limited';
title2 'Sales figures for the European region.';
sum sales;
format sales comma14.2;
run;
/**********************************************************************/
/* Set up DDE file allocation */
filename excel DDE
'Excel|
c:\data\excel\ddetests\[example2.xls]central!R6C1:R15C4';
/* Create some test data */
data aklsales;
input type $1-10 area $12-26 year 28-31 value 33-40;
cards;
Art Deco Mount Eden 1938 250000
Art Deco Mount Albert 1935 284000
Villa Mount Eden 1920 425000
Town House Ponsonby 1992 225000
Villa Epsom 1932 338000
Flats Mount Roskill 1970 212500
;
/* Read from data set and write to Excel spreadsheet */
data _null_;
set aklsales;
file excel notab;
put type '09'x area '09'x year '09'x value;
run;
/**********************************************************************/
/* Set up DDE file allocation */
filename excel DDE
'Excel|
c:\data\excel\ddetests\[example2.xls]central!R6C1:R15C4';
/* Create some test data */
data aklsales;
length type $10 area $15;
input type $1-10 area $12-26 year 28-31 value 33-40;
cards;
Art Deco Mount Eden 1938 250000
Art Deco Mount Albert 1935 284000
Villa Mount Eden 1920 425000
Town House Ponsonby 1992 225000
Villa Epsom 1932 338000
Flats Mount Roskill 1970 212500
;
/* Read from data set and write to Excel spreadsheet */
data _null_;
set aklsales;
file excel;
put type area year value;
run;
/**********************************************************************/
/* Read from data set and write to Excel spreadsheet */
data _null_;
set aklsales;
file excel notab;
put type area year value;
run;
/**********************************************************************/
file excel notab;
put type '09'x area '09'x year '09'x value;
/**********************************************************************/
/* Set options */
options noxwait noxsync;
/* Create some test data */
data aklsales;
input type $1-10 area $12-26 year 28-31 value 33-40;
cards;
Art Deco Mount Eden 1938 250000
Art Deco Mount Albert 1935 284000
Villa Mount Eden 1920 425000
Town House Ponsonby 1992 225000
Villa Epsom 1932 338000
Flats Mount Roskill 1970 212500
;
/* Start up Excel */
x excel;
/* Wait for application to start up */
data _null_;
x=sleep(5);
run;
/* Create a spreadsheet and format */
filename cmds dde 'excel|system';
filename hdgs dde
'Excel|
C:\DATA\EXCEL\DDETESTS\[DDEEX3.XLS]Sheet1!R1C1:R10C5';
data _null_;
file cmds ;
/* Send save as command */
put '[save.as("c:\data\excel\ddetests\ddeex3.xls")]';
/* Select and format title cells */
put '[select("R1C1:R1C4")]';
put
'[format.font("Century Gothic",20,
FALSE,FALSE,TRUE,FALSE,3)]';
put '[select("R2C1:R2C1")]';
put '[format.font("Times Roman",16,
FALSE,FALSE,TRUE,FALSE,1)]';
/* Select and column headings */
put '[select("R4C1:R4C5")]';
put '[format.font("Times Roman",12,
FALSE,FALSE,TRUE,FALSE,5)]';
run;
/* Transfer data to spreadsheet */
/* enter headings */
data _null_;
set aklsales;
file hdgs notab;
if _n_=1 then do;
/* Set up report and column headings */
put "Central Realty Limited";
put 'Housing Sales for the Central Auckland Region.';
put ' ';
put 'Type' '09'x 'Area' '09'x 'Year' '09'x 'Value';
end;
/* Pass data */
put Type '09'x Area '09'x Year '09'x Value;
run;
filename cmds clear;
filename hdgs clear;
/**********************************************************************/
/* Create some test data */
data addr;*address data;
length addr1-addr3 $15 name $20 pname $10;
input empnum 1-7 name $9-23 pname $25-34 addr1 $36-50
addr2 $52-66 @68 addr3 ;
cards;
34961 Mr C. Knox Chris Tape Loop Sectn Audio Products Penrose
22243 Mr S. Carter Shayne Sheet Metal Shp Heavy Products Penrose
29497 Ms S. Bell Sandra Sheet Metal Shp Heavy Products Penrose
;
data leave;*leave data;
today=today();
input empnum current prvyear excess total nanndat ddmmyy.
pcurrent pprvyear pexcess ptotal;
cards;
34961 19.5 20 5.5 45 010995 0 20 25.5 45.5
22243 14.0 15 3.0 32 050995 0 15 18 33
29497 24.0 25 65.5 114.5 020995 0 25 90.5 115.5
;
proc sql;
create table leavelet as
select addr.*, leave.*
from addr, leave
where addr.empnum=leave.empnum;
/**********************************************************************/
filename wordsys DDE 'Winword|system';
filename wordaddr DDE 'Winword|leavelet!address';
filename wordname DDE 'Winword|leavelet!pname';
filename wordbal DDE 'Winword|leavelet!balances';
/**********************************************************************/
%macro letter;
data _null_;
/* Create new document */
file wordsys;
put '[FileNew .Template = "leavelet", .NewTemplate = 0]';
put '[FileSaveAs .Name = "Leavelet.DOC",
.Format = 0, .LockAnnot = 0,
.Password = "",'
'.AddToMru = 1, .WritePassword = "",
.RecommendReadOnly = 0,
.EmbedFonts = 0,'
'.NativePictureFormat = 0, .FormsData = 0]';
run;
/**********************************************************************/
data _null_;
obsnum=&obsnum;
set leavelet point=obsnum;
if _error_ then abort;
date=today();
/* Send date and address data */
file wordaddr notab;
put date ddmmyy. // name / addr1 / addr2 / addr3;
/* Send preferred name */
file wordname notab;
put pname;
/* Send leave balances */
file wordbal notab;
put '09'x 'Currently Accuring' '09'x 'Previous Year'
'09'x 'Excess Leave' '09'x 'Total Leave';
put 'Balances at ' today ddmmyy. '09'x current
'09'x prvyear
'09'x excess '09'x total;
put 'Your next service anniversary is: ' nanndat ddmmyy.;
put 'Predicted Balances at ' nanndat ddmmyy.
'09'x pcurrent '09'x pprvyear
'09'x pexcess '09'x ptotal;
stop;
run;
/**********************************************************************/
data _null_;
file wordsys;
put '[ToolsMacro .Name = "leave1", .Run, .Show = 0,
.Description = "", .NewName = ""]';
put '[FileSave]';
put '[FileClose]';
run;
%mend;
/**********************************************************************/
data _null_;
set leavelet end=last;
if last then call symput('numobs',_n_);
run;
%macro genlet;
%do obsnum=1 %to &numobs;
%letter;
%end;
%mend;
/**********************************************************************/
data _null_;
%genlet;
run;
filename wordaddr;
filename wordname;
filename wordbal;
filename wordsys;
run;
/**********************************************************************/