I know this list overwhelmingly prefers using 'proc import' and 'proc export', so ignore this post if that is your preference.
Creating a highly stylized excel template and replacing, updating and inserting data in place into the template.
This is SAS differentiator (I think SAS would like to deprecate this functionality). WPS/R/Python implementations are not as clean, especially with regard to SAS (set/merge/update/modify) statements.
Win 7 64bit, Office 2010 64bit, SAS 64bit and free MS ODBC/OLEDB drivers)
1. Create a highly stylized template using 'ods excel'. This is just the output of 'ods excel'
Problem: 'ods excel' does not create a 'odbc' table. You need to either manually highlight the data and
create a named range(odbc table) or use the short R SAS macro '%utl_submit_r64' below.
2. Once you have an odbc table you can update, replace or insert data keeping all style elements
To execute the formulas on final sheet you need to go to
formulas- show formulas - format column custom(general) - format formulas
The single most powerful excel 'libname' festure is the automatic creation of an 'odbc/oledb' table with
the same name as the sheet. I think SAS left this out of 'ods excel' to push 'proc export' and EG. Trivial for SAS to add odbc/oldb support to 'ods excel'?
Your template does not have to have a primary key, you could use name. as primary key enables some more sophisticated in place single cell updates.
HAVE (Excel template(no data except key) d:\xls\formulas.xlsx with 'ODBC' table class and sheet class)
===================================================================================
+------------------+------------+------------+-----------+--------------------------+
| | A | B | C | D | E |
+------+-----------+------------+------------+-----------+--------------------------+
| | KEY | NAME | HEIGHT | WEIGHT | BMI |
+------+-----------+------------+------------+-----------+--------------------------+
| | | | | | |
| 1 | 1 | [BOLD] | $##,###.## | ##,###.## |=(D2/(2.2*(C2/39.37)^2)) |
| 2 | 2 | [BOLD] | $##,###.## | ##,###.## |=(D3/(2.2*(C3/39.37)^2)) |
| 3 | 3 | [BOLD] | $##,###.## | ##,###.## |=(D4/(2.2*(C4/39.37)^2)) |
| 4 | 4 | [YELLOW] | [YELLOW] | [YELLOW] | [YELLOW] |
| .. | .. | .. | .. | ... | ... |
| N | .. | .. | .. | ... | ... |
+------------------+------------+------------+-----------+--------------------------+
[CLASS]
WANT (fill in with data and maintain styles)
==============================================
BOLD
+------------------+------------+------------+-----------+-----------------+
| | A | B | C | D | E |
+------+-----------+------------+------------+-----------+-----------------+
| | KEY | NAME | HEIGHT | WEIGHT | BMI |
+------+-----------+------------+------------+-----------+-----------------+
| | | | | | |
| 1 | 1 | Alfred | 690,000 |$1,125,000 | 0.001664802 |
| 2 | 2 | Alice | 565,000 | $840,000 | 0.001853918 |
| 3 | 3 | Barbara | 653,000 | $980,000 | 0.001619227 |
| 4 | 4 | Carol | 628,000 |$1,025,000 | 0.001831103 | YELLOW
| .. | .. | .. | .. | ... | ... |
| N | .. | .. | .. | ... | ... |
+------------------+------------+------------+-----------+-----------------+
[CLASS]
FULL SOLUTION AFTER CREATING TEMPLATE
libname xel "d:/xls/formulas.xlsx" scan_text=no;
data xel.class;
modify xel.class;
set sd1.hasdata;
run;
libname xel clear;
CREATE THE TEMPLATE
===================
%let tpl=d:/xls/template_formulas.xlsx;
%let final_xlsx=d:/xls/formulas.xlsx;
* you need this macro to copy template to another workbook(templates dshoul not have data)
%macro BinCop(
in=\\filesrv04\stat\peggcsf\sd01\meta\datamart\bonepain_2008\docs\rogerdeangelis\bne.xls
,out=c:\pis\bne.xls
) / des="Copy a binary file";
data _null_;
infile "&in" recfm=n;
file "&out" recfm=n;
input byt $char1. @@;
put byt $char1. @@;
run;
%mend BinCop;
data sd1.class(drop=bmi) sd1.hasdata;
retain key 0;
set sashelp.class;
height=10000*height;
weight=10000*weight;
bmi=0;
key=_n_;
output sd1.hasdata;
call missing(of _all_);
key=_n_;
output sd1.class;
keep key name height weight bmi;
run;quit;
%utlfkil(&tpl.);
ods excel file="&tpl.";
ods excel options(sheet_name="class" sheet_interval="none");
proc report data=sd1.class nowd;
column key name height weight bmi;
define key / display;
define name / style(column)={font_weight=bold};
define height / display style={just=right tagattr='format:#,###,##0' cellwidth=1.1in};;
define weight / display style={just=right tagattr='format:$#,###,##0' cellwidth=1.1in};;
define bmi / computed "BMI" format=3. style={tagattr="formula:(RC[-1]/(2.2*(RC[-2]/39.37)^2))"};
compute bmi;
bmi=0;
endcomp;
compute key;
if key in (4,8,12,15) then call define(_row_, "Style", "Style = [background = yellow]");
endcomp;
run;
ods excel close;
%utl_submit_r64("
library(XLConnect);
library(haven);
datnew<-read_sas('d:/sd1/class.sas7bdat');
wb <- loadWorkbook('&tpl.', create = FALSE);
setStyleAction(wb,XLC$'STYLE_ACTION.NONE');
createName(wb, name = 'class', formula = 'class!$A$1');
writeNamedRegion(wb, datnew, name = 'class');
saveWorkbook(wb);
");
;
%bincop(in=&tpl.,out=&final_xlsx.);
libname xel "&final_xlsx" scan_text=no;
data xel.class;
modify xel.class;
set sd1.hasdata;
run;
libname xel clear;
3805 libname xel "&final_xlsx" scan_text=no;
SYMBOLGEN: Macro variable FINAL_XLSX resolves to d:/xls/formulas.xlsx
NOTE: Libref XEL was successfully assigned as follows:
Engine: EXCEL
Physical Name: d:/xls/formulas.xlsx
3806 data xel.class;
3807 modify xel.class;
3808 set sd1.hasdata;
3809 run;
NOTE: There were 19 observations read from the data set XEL.class.
NOTE: The data set XEL.class has been updated.
There were 19 observations rewritten,
0 observations added and
0 observations deleted.
NOTE: There were 19 observations read from the data set SD1.HASDATA.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 474.56k
OS Memory 27164.00k
Timestamp 03/10/2017 07:12:29 AM
Step Count 636 Switch Count 0
3810 libname xel clear;
NOTE: Libref XEL has been deassigned.
proc sql dquote=ansi;
connect to excel (Path="&final_xlsx");
select * from connection to Excel
(
Select
max(len(name)) as lennam
,max(len(key)) as lenkey
from
class
);
disconnect from Excel;
Quit;
lennam lenkey
------------------------
7 2
proc sql dquote=ansi;
connect to excel (Path="&final_xlsx");
select * from connection to Excel
(
Select
count(*) + sum(isnumeric(name)) as numchrname,
count(*) + sum(isnumeric(weight)) as numchrwgt
from
class
);
disconnect from Excel;
Quit;
numchrname numchrwgt
--------------------
19 0
proc sql dquote=ansi;
connect to excel (Path="&final_xlsx");
create table cnv as
select * from connection to Excel
(
Select
name
,str(now()) as curtym
,Switch(name="John","Table",name="Alice","Desk") AS TblDsk
,IIF(Weight>95, "Yes", "No") as onoff
,mid(name,1,1) as nammid
,format(weight,"####.00000000") as lonwgt
from
class
);
disconnect from Excel;
Quit;
proc sql dquote=ansi;
connect to excel (Path="&final_xlsx");
execute(
update `d:/xls/formulas`.class class
set
weight=789
,name='Roger'
where key=2
) by excel;
disconnect from Excel;
Quit;
libname xel "&final_xlsx";
proc print data=xel.class(obs=2);
run;quit;
libname xel close;
Obs KEY NAME HEIGHT WEIGHT
1 1 Alfred 690000 $1,125,000.00
2 2 Roger** 565000 $789.00**
** changed
proc sql dquote=ansi;
connect to excel (Path="&final_xlsx");
execute(insert into class
values( 20, 'Zorba', 111,99)) by excel;
disconnect from Excel;
Quit;
libname xel "&final_xlsx";
proc print data=xel.class;
run;quit;
libname xel close;
libname tbl excel "&final_xlsx" scan_text=no;
proc sql;
update tbl.class
set height=199,
name='Xena'
where key=4;
quit;
libname tbl clear;
libname tbl excel "&final_xlsx" scan_text=no;
proc sql;
update tbl.class
set height=(select sum(height) from tbl.class where key<10)
where key<5;
;quit;
libname tbl clear;
bs from tbl.class total obs=
KEY NAME HEIGHT
1 Alfred 5592000
2 Roger 5592000
3 Barbara 5592000
4 Carol 5592000
5 Henry 635000
6 James 573000
7 Jane 598000
* _ __ __
___| |_ _ _ / _|/ _|
/ __| __| | | | |_| |_
\__ \ |_| |_| | _| _|
|___/\__|\__,_|_| |_|
;
libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
update tbl.demog
set age=199,
sex='none'
where pat=12;
quit;
libname tbl clear;
libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
update tbl.play
set wgt=(select sum(wgt) from tbl.play where pat<88888)
where pat=99999;
quit;
libname tbl clear;
proc sql dquote=ansi;
connect to excel (Path="h:\xls\tables.xls");
execute(
update `h:\xls\Tables`.jyn jyn
set pat=' ',
age=' ',
treat=' ',
lab= ' ',
val= ' '
) by excel;
disconnect from Excel;
Quit;
libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc sql;
create
table trn as
select
put(l.pat,5.) as pat,
put(l.age,5.) as age,
c.treat as treat length=16 format=$16.,
r.lab as lab length=3 format=$3.,
put(r.val,5.2) as val
from
tbl.demog as l left join tbl.treatment as c on l.pat=c.pat
left join tbl.lab as r on l.pat=r.pat
order
by pat;
quit;
proc contents data=tbl.jyn position;
run;
data tbl.jyn;
n=_n_;
modify tbl.jyn;
set trn;
run;
libname tbl clear;
libname tbl excel "h:\xls\tables.xls" scan_text=no;
proc contents data=tbl.sheet2;
run;
proc datasets library=tbl;
modify sheet2;
rename f1=pat;
quit;
proc contents data=tbl.sheet2;
run;
libname tbl clear;
* exporting dataset to an excel sheet
via WScript (vbs). Slow but flexible.
Modified from data _null_^s SAS-L
posting archived at UGA: http://tinyurl.com/yl9bvxb or
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0911a&L=sas-l&O=A&P=13475
chang_y_ch...@hotmail.com 20081104
<iebup...@GMAIL.COM> wrote:
...
>I don't now if this will be acceptable, but it does operate without
>SAS/ACCESS to PC file formats. It does require EXCEL.
...
Hi, _null_,
Thanks for sharing a piece of excellent code. I would add that in order to
run, it also requires Windows Script Host enabled.
In terms of coding styles, it can be made clearer by separating (as much as
we can) the window scripting vbs code from the sas data step code. Here is
my try using a technique of expanding placeholders. I also love to clean up
as much as I can. This ran on sas 9.2 on windows vista with excel 2007
installed locally.
Cheers,
Chang
/* exporting dataset to an excel sheet
via WScript (vbs). Slow but flexible.
Modified from data _null_^s SAS-L
posting archived at UGA: http://tinyurl.com/yl9bvxb or
http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0911a&L=sas-l&O=A&P=13475
chang_y_ch...@hotmail.com 20081104
*/
%let data = sashelp.shoes;
%let pwd = %sysfunc(pathname(WORK));
%let vbs = &pwd\sas2exl.vbs;
%let xls = &pwd\sas2exl.xls;
/* get the variable names and labels */
proc contents data=&data out=contents(keep=name label
rename=(name=_name label=_label)) noprint;
run;
/* write a WSH script in vbs.
substituting placeholders with data, if any */
data _null_;
infile cards;
file "&vbs";
length _inLine _outLine _value $32767;
input;
_inLine = _infile_;
/* line without place holders */
if index(_inLine, "#row#") = 0 then do;
_outLine = _inLine;
link putLine;
return;
end;
/* header row */
do _col = 1 by 1 while (not _headerDone);
set contents end = _headerDone;
_row = 1;
_value = quote(trim(_name));
if not missing(_label) then do;
_newLine = " & vbLF & vbLF & ";
_label = quote(trim(_label));
_value = catx(_newLine, _value, _label);
end;
_valueQuote = 0;
link expand;
link putLine;
end;
_nCols = _col - 1; /* we get to know the n columns */
/* other rows */
do _row = 2 by 1 while (not _bodyDone);
set &data end = _bodyDone;
do _col = 1 to _nCols;
set contents point=_col;
_value = vvaluex(_name);
_valueQuote = 1;
link expand;
link putLine;
end;
end;
return; /* end of main */
/* utilities */
expand:
_value = trim(_value);
if _valueQuote then _value = quote(trim(_value));
_outLine = trim(tranwrd(tranwrd(tranwrd(_inLine,
"#row#", strip(put(_row, best.))),
"#col#", strip(put(_col, best.))),
"#value#", _value)
);
return;
putLine:
_outLine = trim(_outLine);
_lineLength = length(_outLine);
put _outLine $varying. _lineLength;
return;
/* vbs file template */
cards;
' sas2excel.vbs -- automatically generated
' open a sheet
Set app = CreateObject("Excel.Application")
app.Visible = True
app.DisplayAlerts = False
app.Workbooks.Add
' write data out
Set sheet = app.ActiveWorkbook.Worksheets(1)
sheet.Cells(#row#, #col#).Value = #value#
' a little bit of formatting
sheet.UsedRange.columns.autofit
' save the excel sheet and clean up
app.DisplayAlerts = False ' to overwrite
app.ActiveWorkbook.SaveAs(WScript.Arguments(0))
app.Quit
set sheet = nothing
set app = nothing
;
run;
/* call the window script -- this should open excel */
filename cscript pipe "cscript ""&vbs"" ""&xls""";
data _null_;
infile cscript end=end;
do while(not end);
input;
put _infile_;
end;
stop;
run;
filename cscript clear;
"Data _null_;" View profile
More options Nov 4, 2:15 pm
Newsgroups: comp.soft-sys.sas
From: iebup...@GMAIL.COM ("Data _null_;")
Date: Wed, 4 Nov 2009 16:15:44 -0600
Local: Wed, Nov 4 2009 2:15 pm
Subject: Re: comment on recent sample 35574 at SAS
Reply | Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author
Hi Chang,
Good points all. I liked your technique of expanding placeholders
when I first saw you post it and have used it from time to time.
Getting the names in a data set is good too. I just wanted to use
VNEXT. I think that instead of CONTENTS you could use TRANPOSE to
build the CONTENTS data set. The will allow the order of variables to
be explicitly specified or defaulted to data set order with _ALL_.
You would have to make sure _LABEL is defined in the next data step as
PROC TRANSPOSE won't create the variable if there are no labels.
proc transpose data=&data(obs=0) name=_name label=_label out=contents;
var _all_;
run;
It is indeed slow, but a bit faster with ?Visible=False?.
Best regards, data _null_;
On 11/4/09, Chang Chung <chang_y_ch...@hotmail.com> wrote:
