[prev in list] [next in list] [prev in thread] [next in thread]
List: sas-l
Subject: SAS Forum: ODS Excel Ehancements: Insert reports or images are arbitrary positions
From: Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date: 2016-08-28 0:22:17
Message-ID: 6268168977612368.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]
SAS Forum: ODS Excel Ehancements: Insert reports or images are arbitrary positions
Exciting news on ODS Excel under Windows 7 64bit SAS 9.4M2 (preproduction?)
to see final graph and table placed at an arbitrary position (B2 and B24)
https://drive.google.com/file/d/0ByX2ii2B0Rq9c2J5WE9MWTNHOEU/view?usp=sharing
SOAPBOX ON
Toward the end of the video, shown below, the camera panned the audience. I was
discouraged to see the age of the audience, it was like looking in the mirror.
We need to excite younger programmers. Integration of Python and R with the world \
class SAS/WPS and solving problems that require combinations of products might help.
It does work because I have done presentations(SAS/R/Python) to mixed age audiences \
and the result was positive, with the younger guys(Pythion R) coming up to talk to me
after the presentation. SAS/WPS-TESSERACT, Python NLP, Python USAddresses, \
Python-Openxl for instance. I work on SAS Servers with Big Data combined with a power \
workstation(most new computers), where SAS/WPS are a must. I use the term Big Data to \
mean petabyte databases.
SAS dropping down nicely to Open source R and Python with something like common \
storage blocks COMMON X[*] Y[*] SEX AGE ...
might be a start.
SAS/WPS could focus more on its core products and close out PC SAS(keep workstation \
SAS), IML(R), SAS Studio, Access to PC files(Python /R are better?) , IOM, Microsoft \
ofice integration, SAS/Internet.... Drop many of the SAS boutique products.
Keep Server EG for non-programmers, production jobs and as a data server for power \
workstations. Power workstations would do adhoc tasks and development programming. \
And for Gods sake stop this 'SAS only lockdown mode' or at least give away 'non \
lockdown SAS ' on power workstations connected to any lockdown server site. Also \
come out with a persistent high speed 'sas connect ike' connection from server to \
workstation. It is my belief that this woud be more secure because 'lockdown' can \
force users to find hidden insecure ways to solve problems.
SOAPBOX OFF
SAS SASGF 2016 presenattion that inspired me
https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Excel-GRAPHS/m-p/292901
This was the last and best session I attended at SASGF 2016
http://sasgf16.v.sas.com/detail/videos/breakout-sessions/video/4856221836001/sas5642---a-ringside-se...
And corresponding paper:
http://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf
And documentation
http://support.sas.com/documentation/cdl/en/odsug/67921/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04...
It is also possible to do this with R and Python, both support Windows, Unicies and \
OSx
ODS excel is a very exciting product.
excel enhance macro ( I made some changes to the VBS code. Had some issues with
freezes and open excel applivation - May have been my problem)
ftp://ftp.sas.com/techsup/download/base/SGF2016_SAS177.ZIP
I wrote a lex flexible AB macro, see below.
It only handles XLSX and one graph at a time.
*************************************************************************************************
*************************************************************************************************
HAVE1 An excel report created by ods excel
Predicted
Country Product Actual Sales Sales
CANADA BED $47,729.00 $44,215.00
CHAIR $50,239.00 $46,796.00
DESK $52,187.00 $49,393.00
SOFA $50,135.00 $45,726.00
TABLE $46,700.00 $46,889.00
GERMANY BED $46,134.00 $43,796.00
CHAIR $47,105.00 $44,069.00
DESK $48,502.00 $44,639.00
SOFA $55,060.00 $49,517.00
TABLE $49,197.00 $49,533.00
U.S.A. BED $48,174.00 $49,856.00
CHAIR $50,936.00 $45,245.00
DESK $48,543.00 $52,163.00
SOFA $43,393.00 $45,208.00
TABLE $46,303.00 $49,250.00
$730,337.00 $706,295.00
HAVE2 and an existing ODS graoh
Plot of Y*X. Legend: A = 1 obs, B = 2 obs, etc.
Y |
|
1.0 + AIIHJFZFIHJIA
| JJIH HIIK
| GJH HJG
| FLG FLG
| DIC BJD
0.8 + HG GH
| BMA ALC
| BO OB
| DM MD
| CJ ID
0.6 + BJ JB
| J J
| JB AK
| BH GC
| P P
0.4 + EG NLNLJZJMNLN GE
| I LNLB AMNL I
| L MQB BPN KA
| I IQA AQI I
| K OG GO K
0.2 + I XA AX I
| K PC BQ JA
|J PB BP J
|K JI IJ K
|F P OA F
0.0 +Z Q FQSPZOSQG Q Z
|F P CSRI HRSD P F
|L MA TR QU N L
|I P EVD DUF P I
| K O DZ YE O JA
-0.2 + I N Z ZA KC I
| K N W W N K
| IA X JM LK X J
| K X S S X K
| J M R R M J
-0.4 + DG O QB S LC GD
| Q N U U N Q
| AI P X X P HB
| IB MB W W AN AJ
| J O U U O J
-0.6 + BJ Q QB S Q JB
| CJ P R R OA ID
| DN JJ T T JJ ND
| AO OB IM LJ BO OA
| BMA PD WA X CQ ALC
-0.8 + HG WB Z Z BW GH
| DICNHCZ ZDHNBJD
| FLPQFVE EUGQOLG
| FJTRVT SWQUJF
| IUXZUJ ITZYTJ
-1.0 + VZZZZZZZZZV
|
-+------+------+------+------+------+------+------+------+-
-1.008 -0.756 -0.504 -0.252 0.000 0.252 0.504 0.756 1.008
WANT (To position the Report at cell B4 and grapg at B24 )
REPORT AT B4 and plot at B24
EXCEL EXCE EXCEL
ROW COL COL
A B
01
02
03
04 Predicted
05 Country Product Actual Sales Sales
06 CANADA BED $47,729.00 $44,215.00
07 CHAIR $50,239.00 $46,796.00
08 DESK $52,187.00 $49,393.00
09 SOFA $50,135.00 $45,726.00
10 TABLE $46,700.00 $46,889.00
11 GERMANY BED $46,134.00 $43,796.00
12 CHAIR $47,105.00 $44,069.00
13 DESK $48,502.00 $44,639.00
14 SOFA $55,060.00 $49,517.00
15 TABLE $49,197.00 $49,533.00
16 U.S.A. BED $48,174.00 $49,856.00
17 CHAIR $50,936.00 $45,245.00
18 DESK $48,543.00 $52,163.00
19 SOFA $43,393.00 $45,208.00
20 TABLE $46,303.00 $49,250.00
21 $730,337.00 $706,295.00
22
23
24 Plot of Y*X. Legend: A = 1 obs, B = 2 obs, etc.
25
26 Y |
27 |
28 1.0 + AIIHJFZFIHJIA
29 | JJIH HIIK
30 | GJH HJG
31 | FLG FLG
32 | DIC BJD
33 . ...
34
35 - 0.8 + HG WB Z Z BW GH
36 | DICNHCZ ZDHNBJD
37 | FLPQFVE EUGQOLG
38 | FJTRVT SWQUJF
39 | IUXZUJ ITZYTJ
40 - 1.0 + VZZZZZZZZZV
41 |
42 -+------+------+------+------+------+------+------+------+-
43 -1.008 -0.756 -0.504 -0.252 0.000 0.252 0.504 0.756 1.008
SOLUTION
* Create report position report at starting at cell B4 (see options statement);
ods excel file="d:/xls/class1.xlsx" options(start_at="b4");
ods excel options(sheet_name="sheet1");
proc report data=sashelp.prdsale spanrows ;
column country product actual predict;
define country / group;
define product / group;
rbreak after / summarize;
Run;
ods excel close;
* parametic data for plotting;
data para;
do x=-1 to 1 by .001;
do y=-1 to 1 by .001;
if round(x**2+y**2,.0005)=1 then output;
if round(x**2+y**2,.0005)=.5 then do;yy=y; y=y-.3; output;y=yy;end;
if round(x**2+y**2,.0005)=.25 then do;yy=y; y=y-.5; output;y=yy;end;
end;
end;
run;
* produce the line printer graph;
options ls=64 ps=64;
proc plot;
plot y*x;
run;
* produce sqplot;
ods graphics on / reset imagename="paracircles" border=off
width=6in height=6in antialias ANTIALIASMAX=32100 ;
ods listing gpath='d:/png' image_dpi=200;
proc sgplot data=para;
scatter x=x y=y / markerattrs=(symbol=squarefilled size=8);
run;quit;
ods listing;
ods graphics off;
* This is my very beta macro, but you can also use the SAS macro;
%utl_graphat(
xlsinp =d:\xls\class1.xlsx
,graphat =sheet1!B24
,graph =d:\png\paracircles.png
,xlsout =d:\xls\class1graph.xlsx
);
My Macro
%macro utl_graphat(
xlsinp =d:\xls\class1.xlsx
,graphat =sheet1!B24
,graph =d:\png\xlconnect_class.png
,xlsout =d:\xls\class1graph.xlsx
) / des="add graph at arbitrary posion in excel sheet";
%local sheet celstart rc;
/* for testing without macro
%let xlsinp =d:\xls\class1.xlsx;
%let graphat =sheet1!B24;
%let graph =d:\png\xlconnect_class.png;
%let xlsout =d:\xls\class1graph.xlsx;
*/
* Chang Chung - probably should check if output XLSX directory exists - let SAS \
prodece the ERROR message; %put %sysfunc(ifc(%sysevalf(%superq(xlsinp \
)=,boolean),**** Please Provide existing excel XLSX input file ****,)); %put \
%sysfunc(ifc(%sysevalf(%superq(graphat)=,boolean),**** Please Provide Starting cell \
for graph ****,)); %put %sysfunc(ifc(%sysevalf(%superq(graph )=,boolean),**** \
Please Provide Input Graph ****,)); %put \
%sysfunc(ifc(%sysevalf(%index(&graphat,%str(!))=0,boolean),**** Please Provide \
starting position of graph sheet1!B22 ****,)); %put \
%sysfunc(ifc(%sysevalf(%superq(xlsout )=,boolean),**** Please Provide new excel XLSX \
output file ****,));
%let res= %eval
(
%sysfunc(ifc(%sysevalf(%superq(xlsinp )=,boolean),1,0))
+ %sysfunc(ifc(%sysevalf(%superq(graphat)=,boolean),1,0))
+ %sysfunc(ifc(%sysevalf(%superq(graph )=,boolean),1,0))
+ %sysfunc(ifc(%sysevalf(%superq(xlsout )=,boolean),1,0))
+ %sysfunc(ifc(%sysevalf(%index(&graphat,%str(!))=0,boolean),1,0))
);
%if "&res" ="0" %then %do;;
* delete if vbs file exists;
filename grfatvbs "%sysfunc(pathname(work))\utl_graphat.vbs";
%let rc=%sysfunc(ifc(%sysfunc(fexist(grfatvbs)),%sysfunc(fdelete(grfatvbs)),%str( \
))); filename grfatvbs clear;
* delete if output xls outfile exists;
filename grfatxls "&xlsout.";
%let rc=%sysfunc(ifc(%sysfunc(fexist(grfatxls)),%sysfunc(fdelete(grfatxls)),%str( \
))); filename grfatxls clear;
data _null_;
file "%sysfunc(pathname(work))\utl_graphat.vbs";
x1= 'Set objExcel = CreateObject("Excel.Application")';
x2= 'objExcel.Visible = True';
x3= 'objExcel.DisplayAlerts=False';
x4= resolve('Set objWorkbook1= objExcel.Workbooks.Open("&xlsinp.")');
x5= resolve('Set Xlsheet = objWorkbook1.Worksheets("sheet1" )');
x6= resolve('Xlsheet.Range("&celbeg." ).Activate');
x7= resolve('Xlsheet.Pictures.Insert("&graph." )');
x8= resolve('objWorkbook1.saveAs "&xlsout.",51');
x9= 'objWorkbook1.close';
x10= 'objExcel.DisplayAlerts=True';
x11= 'objExcel.Application.quit';
array cmd x:;
do over cmd;
put cmd;
putlog cmd;
end;
run;quit;
systask kill utl_graphat;
systask command "%sysfunc(pathname(work))\utl_graphat.vbs"
taskname=utl_graphat status=utl_graphatstatus wait \
mname=utl_graphatprocess;
%put &=utl_graphatstatus;
%put &=utl_graphatprocess;
%put &=sysrc;
%put %sysfunc(ifc(utl_graphatstatus=0,*** SYSTASK COMPLETED ***,*** SYSTASK \
COMPLETED *** ));
%put %sysfunc(ifc(&sysrc=0,*** SYSTASK COMPLETED SUCESSFULLY***,*** SYSTASK \
COMPLETED *** ));
%end;
%mend utl_graphat;
%utl_graphat;
%macro Excel_enhance(open_workbook=,
insert_workbook=,
insert_sheet=,
insert_image=,
create_workbook=,
file_format=);
%local open_workbook insert_sheet insert_image create_workbook file_format;
%let script_loc=%sysfunc(getoption(WORK))\enhance.vbs;
data _null_;
file "&script_loc";
put "Set objExcel = CreateObject(""Excel.Application"") ";
put "objExcel.Visible = True ";
put "objExcel.DisplayAlerts=False";
put "Set objWorkbook1= objExcel.Workbooks.Open(""&open_workbook"")";
%if &insert_image ne %then %do;
%let pic_count=%sysfunc(countc(&insert_image,","));
%if %sysfunc(countc(&insert_image,","))=0 %then %do;
%let sheet_name=%sysfunc(scan(&insert_image,2,"#")));
%let sheet_name=%sysfunc(scan(&insert_image,2,"#"));
%let range_field=%sysfunc(scan(&insert_image,2,"!"));
%let image_loc=%sysfunc(scan("&insert_image",1,"#"));
sheet_name=quote(scan("&sheet_name",1,"!"));
range_field=quote("&range_field");
image_loc=quote("&image_loc");
put "Set Xlsheet = objWorkbook1.Worksheets(" sheet_name ")";
put "Xlsheet.Range(" range_field ").Activate";
put "Xlsheet1.Pictures.Insert(" image_loc ")";
%end;
%else %do;
%let image_count=%sysfunc(countc(&insert_image,","));
%let image_count=%eval(&image_count+1);
%do j=1 %to &image_count;
%let insert_image&j=%sysfunc(scan(&&insert_image,&j,","));
%let sheet_name&j=%sysfunc(scan(&&insert_image&j,2,"#"));
%let \
sheet_name&j=%quote(%str(%")%sysfunc(scan(&&sheet_name&j,1,"!"))%str(%"));
%let \
range_field&j=%quote(%str(%")%sysfunc(scan(&&insert_image&j,2,"!"))%str(%"));
%let \
image_loc&j=%quote(%str(%")%sysfunc(scan("&&insert_image&j",1,"#"))%str(%"));
sheet_name=quote(scan("&&sheet_name&j",1,"!"));
range_field=quote("&&range_field&j");
image_loc=quote("&&image_loc&j");
put;
put "Set Xlsheet = objWorkbook1.Worksheets( &&sheet_name&j)";
put "Xlsheet.Range(&&range_field&j).Activate";
put "Xlsheet.Pictures.Insert(&&image_loc&j)";
%end;
%end;
%end;
%if &insert_workbook ne %then %do;
put;
put "set objWorkbook2=objExcel.Workbooks.Open(""&insert_workbook"")";
%let sheet_count=%sysfunc(countc(&insert_sheet,","));
%let sheet_count=%eval(&sheet_count+1);
%do i=1 %to &sheet_count;
%let x=%sysfunc(scan(&insert_sheet,&i));
%if &i=1 %then
%let s&i=%sysfunc(quote(&x));
%else
%let s&i=&s%eval(&i-1),%sysfunc(quote(&x)) ;
%let list=%nrbquote(&&s&i);
%end;
put "set sheetsToCopy=objWorkbook2.Sheets(Array(%quote(&list))) ";
put "sheetsToCopy.Copy objWorkbook1.Sheets(1) ";
%end;
%if &file_format ne %then %do;
%if &file_format=xlsx %then %let file_formatn=51;
%else %if &file_format=xls %then %let file_formatn=1;
%else %if &file_format=csv %then %let file_formatn=16;
%end;
%if "&create_workbook" ne %then %do;
%if &file_format ne %then
%let save="objWorkbook1.saveAs ""&create_workbook"",&file_formatn";
%else
%let save="objWorkbook1.saveAs(""&create_workbook"")";
put &save;
%end;
%else %do;
%let name=%sysfunc(scan(%sysfunc(reverse(%sysfunc(scan(%sysfunc(reverse(&open_workbook)),2)))),1,"."))_update.&file_format;
%put &name;
%if &file_format ne %then
%let save="objWorkbook1.saveAs ""&name"",&file_formatn";
%else
%let save="objWorkbook1.saveAs(""&name"")";
put &save ;
%end;
put "objWorkbook1.close";
put "objExcel.DisplayAlerts=True";
put "set objExcel=nothing";
run;
x "'&script_loc\'";
%mend;
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic