Hi,
I am running a macro, using the "fresh" ODS EXCEL to output two tables on the same worksheet and this is getting repeated by th macro for several datasets.
ODS EXCEL outputs the pair of tables just fine across different worksheets (of the same workbook) just fine.
The first table up the second under.
Now I need to place the tables not vertically but horizontally, side-by-side.
In the previous version
ods tagsets.msoffice2k_x
an option PANCOLS= n could be used to solve the problem.
Unfortunately I fail to find a smilar option for the new ODS EXCEL.
Any advise would be more than welcome!
Thank you.
Dear Ksarp,
My code is attached.
Actually I want the 2nd print output
" proc print data=LIB_IMP.&dn (obs=10);
run;"
to be placed side-by-side to the first output say at K5 cell of the worksheet.
For that purpose I added "ods excel options(sheet_name="%scan(&dn,1,_)" start_at="K5" );" just before the 2nd proc printbut nothing happened.
Then I read this link
Experimenting with ODS EXCEL to create spreadsheets from SAS - The SAS Dummy
I can't figure out what I am doing wrong.
Thank you.
===================================================================================
ods excel file="c:\temp\multitablefinal.xlsx"
options(sheet_interval="none" contents="yes" embedded_titles="yes" PAGE_ORDER_ACROSS= "ON" );
%macro sample1(dn, label=no);
data &dn;
set LIB_IMP.&dn;
array change _numeric_;
do over change;
if change=0. then
change=.;
end;
run;
* formats to group observations;
proc format;
value nm . = '0' other = '1';
value $ch ' ' = '0' other = '1';
run;
* PROC FREQ produces data set TABLES with counts of missing/non-missing values;
ods listing close;
ods exclude all;
ods output onewayfreqs=tables;
proc freq data=&dn;
tables _all_ / missing;
format _numeric_ nm. _character_ $ch.;
/*title "&dn";*/
run;
ods output close;
ods exclude none;
ods listing;
*place variable labels in a data set;
proc contents data=&dn noprint
out=l&dn (keep=name label format length rename=(name=var) index=(var));
run;
* create a macro variable &LABEL_EXIST ... value 1 if there are labels;
data _null_;
length check $5000;
set l&dn end=last;
check = cats(check,label);
if last then
call symputx('label_exist',(lengthn(check) gt 0));
run;
* create the missing data report as a data set;
data r&dn;
length var $32;
do until (last.table);
set tables;
by table notsorted;
array names(*) f_:;
select (names(_n_));
when ('0')
do;
miss = frequency;
p_miss = percent;
end;
when ('1')
do;
ok = frequency;
p_ok = percent;
end;
end;
end;
miss = coalesce(miss,0);
ok = coalesce(ok,0);
p_miss = coalesce(p_miss,0);
p_ok = coalesce(p_ok,0);
var = scan(table,-1);
set l&dn key=var/unique;
keep var label format length miss ok p_:;
format miss ok comma7. p_: 5.1;
label
miss = 'N_MISSING'
ok = 'N_OK'
p_miss = '%_MISSING'
p_ok = '%_OK'
var = 'VARIABLE'
label = 'LABEL'
;
run;
*** EXPORT TO EXCEL ***;
ods excel options(sheet_name="%scan(&dn,1,_)" );
proc print data=r&dn label /* noobs*/;
%if &label ne no and &label_exist ne 0 %then
%do;
id var label;
var label format length miss p_miss ok p_ok;
title "%scan(&dn,1,_)";
%end;
%else
%do;
id var;
var label format length miss p_miss ok p_ok;
title "%scan(&dn,1,_)";
%end;
run;
ods excel options(sheet_name="%scan(&dn,1,_)" start_at="K5" );
proc print data=LIB_IMP.&dn (obs=10);
run;
/* A dummy table is created. */
ods excel options(sheet_interval="output");
ods exclude all;
data _null_;
dcl odsout obj();
run;
ods select all;
ods excel options(sheet_interval="none");
%mend sample1;
proc contents data=LIB_IMP._all_ noprint out=contents;
run;
data _null_;
set contents;
by memname;
if first.memname;
/* call execute(cats('title "memname";'));*/
call execute(cats('%sample1(',memname,')'));
run;
ods excel close;
===================================================================================
/* T1002940 Output to Excel, multiple 'proc reports' side by side on one sheet ( R xlconnect)
see sheet3 in
https://dl.dropboxusercontent.com/u/15716238/class1.xlsx
* you can use R to drop sheet1 and sheet2
You should be able to paste the R code below into SAS 9.4M2 with IML/R
Here is an solution for two reports side by side reports in one sheet
1. Create sheet1 and sheet2 with your reports using ods excel
2. Use R to combine the reports in sheet3
* note you can do it all in R but I think you want to use SAS to create the reports.
ODS excel seems better than anything i r?Python or Perl for stylized excel reports.
* Maybe 9.4M3 supports 'ODS start at' on the same sheet? This would eliminate
the call to R.
see
https://goo.gl/T3HJte
https://communities.sas.com/t5/ODS-and-Base-Reporting/Generate-multiple-side-by-side-reports-in-singal-excel-sheet/m-p/338440/highlight/false#M18073
http://goo.gl/1r0OgO
https://communities.sas.com/t5/ODS-and-Base-Reporting/Output-to-Excel-multiple-procs-on-a-sheet/m-p/297849#M16852
1 create your reports using ODS excel
Maybe in 9.4M3 the start_at ODS option will work, but for now
you can sort of do it with SAS and R. Not all formatting is copied.
This is by no means perfect. If you have a template
built into sheet3 this will use the template. I had to
create the USD format and apply it.
HAVE (TWO EXCEL SHEETS)
=======================
EXCEL A B C
ROW ---------|-----------|---------------
1 Country Product Actual Sales
2 CANADA BED $47,729.00
3 CHAIR $50,239.00
4 DESK $52,187.00
5 SOFA $50,135.00
6 TABLE $46,700.00
7 GERMANY BED $46,134.00
8 CHAIR $47,105.00
9 DESK $48,502.00
------
SHEET1
------
EXCEL A B C
ROW ---------|-----------|---------------
Predicted
1 Country Product Sales
2 CANADA BED $44,215.00
3 CHAIR $46,796.00
4 DESK $49,393.00
5 SOFA $45,726.00
6 TABLE $46,889.00
7 GERMANY BED $43,796.00
8 CHAIR $44,069.00
9 DESK $44,639.00
SOFA $49,517.00
------
SHEET2
------
WANT THE REPORTS TO BE SIDE BY SIDE IN SHEET3
(We could hvae added sheet1 to sheet2 but it
is better to create sheet3 and drop sheet1 and 2.
==================================================
EXCEL A B C F G H
ROW ---------|-----------|--------------- ---------|-----------|---------------
Predicted
1 Country Product Actual Sales Country Product Sales
2 CANADA BED $47,729.00 CANADA BED $44,215.00
3 CHAIR $50,239.00 CHAIR $46,796.00
4 DESK $52,187.00 DESK $49,393.00
5 SOFA $50,135.00 SOFA $45,726.00
6 TABLE $46,700.00 TABLE $46,889.00
7 GERMANY BED $46,134.00 GERMANY BED $43,796.00
8 CHAIR $47,105.00 CHAIR $44,069.00
9 DESK $48,502.00 DESK $44,639.00
SOFA $49,517.00
SHEET1
SOLUTION
* CREATE TWO SAS REPORTS (in sheet1 and sheet2)
%utlfkil(d:/xls/class1.xlsx);
ods excel file="d:/xls/class1.xlsx";
ods excel options(sheet_name="sheet1" start_at="A1");
proc report data=sashelp.prdsale;
column country product actual;
define country / group;
define product / group;
rbreak after / summarize;
run;quit;
* you cannot output to the same sheet - ignores sheet1 and puts the
report in sheet2 (9.4M2);
* I believe this is supposed to work in SAS 9,4M3?;
* Does not work in 9.4M2;
ods excel options(sheet_name="sheet1" start_at="A1");
proc report data=sashelp.prdsale ;
column country product predict;
define country / group;
define product / group;
rbreak after / summarize;
Run;
ods excel close;
Use R to put them side by side;
%utl_submit_r64('
library(XLConnect);
wb <- loadWorkbook("d:/xls/class1.xlsx");
createSheet ( wb , "sheet3" );
prcntg <- createCellStyle(wb);
setDataFormat(prcntg, format = "$00,000.00");
sheet1 = readWorksheet(wb, sheet = getSheets(wb)[1]);
sheet2 = readWorksheet(wb, sheet = getSheets(wb)[2]);
writeWorksheet(wb,sheet1,sheet="sheet3",startCol=1,header=T);
writeWorksheet(wb,sheet2,sheet="sheet3",startCol=6,header=T);
setCellStyle(wb, sheet = "sheet3", row = 1:17, col = 3, cellstyle = prcntg);
setCellStyle(wb, sheet = "sheet3", row = 1:17, col = 8, cellstyle = prcntg);
saveWorkbook(wb,"d:/xls/class1.xlsx");
');
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Peter, thanks for the comment. Currently, the START_AT option does not allow you to modify the "output position" in mid-sheet. I expect that what you are observing is ODS EXCEL respecting only the last START_AT option that you specify.