SOURCE : sastoxl.sas
PURPOSE : To pour the contents of a SAS data set into a MS Excel spread-
sheet using DDE. The resulting spreadsheet receives some basic
formatting and is saved to a specified location. Values are
entered with their formats applied, whenever there is one
defined on the data set. An existing Excel file can be written
to, rather than a blank document. Custom sheetnames are sup-
ported, either existing ones or new ones ...
CREATED : 08DEC1999 by Koen Vyverman
MODIFIED : 31DEC2000 by Koen Vyverman
Re-wrote some of the macro decision code in a more canonical
form that will ensure proper SASv8 functioning.
04JAN2001 by Koen Vyverman
Added quite a slab of code to take care of custom sheet-names
in the output spreadsheet. Introduced some long filenames in
the process, so this now no longer works with SAS v6.12 and
earlier ... Can be easily repaired though if necessary.
In the same go, removed all traces of the early WAPTWAP me-
thodology for resolving macro-variables before throwing them
at the DDE-link. Now, resolution is forced by means of dummy
text-variables (see DDECMD and MACCMD in the code ...)
USAGE : Macro parameters :
libin (required): SAS library where the input SAS data set
lives.
dsin (required): Name of the SAS data set (part of) which
needs to be dumped into Excel format.
cell1row (optional): The row number of the first cell of the XL
spreadsheet where data should be inserted.
i.e. the upper left corner of the output
data range.
Default = 1.
cell1col (optional): The column number of the first cell of the
XL spreadsheet where data should be in-
serted.
i.e. the upper left corner of the output
data range.
Default = 1.
nrows (optional): The number of rows/observations to be in-
serted. If none is specified, an attempt
will be made to insert all observations
into XL.
Needless to say, this number needs to be
smaller than the maximal number of rows
supported by Excel...
ncols (optional): The number of columns/variables to be in-
serted. If none is specified, an attempt
will be made to insert all variables into
XL.
Needless to say, this number needs to be
smaller than the maximal number of columns
supported by Excel...
tmplpath (optional): The full path to the directory where the
Excel spreadsheet resides to which the
data needs to be written. To be used in
conjunction with TMPLNAME. If none is spe-
cified, a standard new XL workbook will be
used. Do _not_ end the path with a back-
slash character.
tmplname (optional): The filename of the Excel workbook in the
directory specified by TMPLPATH to which
the data needs to be written. To be used
in conjunction with TMPLPATH. If none is
specified, a standard new XL workbook will
be used. Do _not_ end the name with a .xls
filename extension.
sheet (optional): The name of the worksheet within the Excel
workbook to which the data will be writ-
ten. When left blank, this defaults to a
name of the form 'SheetN' where N is the
smallest available positive integer not
yet in use in the Excel workbook. Just try
some, pretty cool :-)
savepath (optional): The full path to the directory where the
finalized Excel workbook needs to be
saved. May be used independently from
SAVENAME. Do _not_ end the path with a
back-slash character.
Default = c:\temp
savename (optional): The filename by which the finalized Excel
workbook should be saved in the directory
specified by SAVEPATH. May be used inde-
pendently from SAVEPATH.
Default = SASTOXL Output
stdfmtng (optional): Standard formatting flag. Off by default.
Give a value of 1 to turn on. This will
apply some basic formatting to the
inserted data. The label row will be bol-
dened. Font will be set to Courier. Column
width will be set to best fit. Freeze
panes will be turned on for the label row.
EXAMPLE : 1) Suppose the data set WORK.SOMETHNG needs to be exported to
an Excel spreadsheet, and saved as 'c:\temp\Some data.xls'.
The data should end up in a worksheet with the default name
'Sheet1', and have the standard formatting applied to them.
To accomplish this, submit the following macro call:
%sastoxl(
libin=work,
dsin=somethng,
savepath=c:\temp,
savename=Some Data,
stdfmtng=1
);
2) Suppose only the first 125 rows of data set WORK.SOMETHNG
need to be exported to an existing Excel spreadsheet, and
saved as 'c:\temp\Some data.xls'. Suppose the full path and
name of the document in which the data need to be inserted
is 'n:\sasok\data\blank dox\Serious Fun.xls', and the block
of data is wanted at row 37, column 3 of a worksheet named
'Stuff from SAS'. If 'Stuff from SAS' does not exist yet on
'Serious Fun.xls', then it should be added as an extra sheet.
To accomplish this, submit the following macro call:
%sastoxl(
libin=work,
dsin=somethng,
cell1row=37,
cell1col=3,
nrows=125,
tmplpath=n:\sasok\data\blank dox,
tmplname=Serious Fun,
sheet=Stuff from SAS,
savepath=c:\temp,
savename=Some Data,
stdfmtng=1
);
CAVEAT : Specifying either TMPLPATH or TMPLNAME without the other will
result in both values being reset to their default settings. As
a consequence, a standard new document will be used.
Specifying a SHEET that already exists on the target workbook
will result in the content thereof (if any) being at least
partially over-written by the exported SAS data. Pay attention.
The names of worksheets in an Excel workbook are _not_ case
sensitive, even if they look as if they are because one is
allowed to use mixed case in the naming. The same goes for the
filenames under Windows by the way ...
</P>
扫码加好友，拉您进群
全部版块
我的主页
收藏
[payto](seller)@(/seller)(subject)(/subject)(body)(/body)(price)1(/price)(transport)1(/transport)(ordinary_fee)(/ordinary_fee)(express_fee)(/express_fee)[/payto]
