Date:         Tue, 2 Dec 2008 14:21:27 -0600
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Creating Multiple-Sheet Excel Workbooks with SAS ODS
Comments: To: "Dana M." <dana.mccoskey@GMAIL.COM>
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

Here's a complete example; I don't see that you are specifying the file name
before the options when you
declare the file, so that could be part of the problem.

-Mary

%macro do_calls_report;
ods listing close;
ods tagsets.excelxp
file='C:\Work_Activities\injections_study_patients\results_report2.xml'
style=analysis
options(absolute_column_width='10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10'
sheet_label=' ');

proc sql;
create table bylist as
select distinct isv_study_nbr, substr(isv_study_nbr,5,2) as year
from isv_injection_study_visits;
quit;
run;

proc sort data=bylist;
by year isv_study_nbr;
run;

data bylist;
set bylist;
obsnum + 1;
run;

proc sql noprint;
select count(*) into :model_count
from bylist;
quit;
%put &model_count;

%Do I = 1 %To &model_count;
proc sql noprint;
select isv_study_nbr into :sheetname
from bylist
where obsnum =&i;
quit;

data results;
set isv_injection_study_visits;
if isv_study_nbr= "&sheetname";
run;

ods tagsets.excelxp options(sheet_name="&sheetname");

proc report data=results nowindows
style(report)=[rules=all cellspacing=0 bordercolor=lightgreen]
style(header)=[background=lightskyblue foreground=black]
style(column)=[background=white foreground=black vjust=top];
column
isv_study_nbr
isv_visit_location
isv_visitdate
isv_age
isv_fa
isv_od_va
isv_od_va_logmar
isv_od_first_logmar
isv_od_logmar_diff
calc_od_logmar_diff_formatted
isv_od_treatment
isv_od_log_er_nomaint
isv_od_log_er_maint
isv_od_log_dr
isv_od_log_nr
isv_od_log_nc
isv_od_ph_mr
isv_od_fa_kg_comments
isv_od_oct_cmt
isv_od_oct_kg
isv_od_rott
isv_od_stage_comments
isv_od_er_nomaint
isv_od_er_maint
isv_od_dr
isv_od_nr
isv_od_nc
isv_os_va
isv_os_va_logmar
isv_os_first_logmar
isv_os_logmar_diff
calc_os_logmar_diff_formatted
isv_os_treatment
isv_os_logmar_er_nomaint
isv_os_logmar_er_maint
isv_os_logmar_dr
isv_os_logmar_nr
isv_os_logmar_nc
isv_os_ph_mr
isv_os_fa_kg_comments
isv_os_oct_cmt
isv_os_oct_kg_comments
isv_os_rott
isv_os_stage_comments
isv_os_er_nomaint
isv_os_er_maint
isv_os_dr
isv_os_nr
isv_os_nc
isv_medical_information ;
define isv_study_nbr/display 'Study Nbr';
define isv_visit_location/display 'Visit Location';
define isv_visitdate/display 'Date of Visit';
define isv_age/display 'Age';
define isv_fa/display 'FA';
define isv_od_va/display 'VA OD';
define isv_od_va_logmar/display 'VA OD LogMar';
define isv_od_first_logmar/display 'First OD LogMar';
define isv_od_logmar_diff/display noprint 'Logmar OD Diff';
define calc_od_logmar_diff_formatted/computed 'Logmar OD Diff';
define isv_od_treatment/display 'Treatment OD';
define isv_od_log_er_nomaint/display 'LogMar Early Responder NoMaint OD';
define isv_od_log_er_maint/display 'LogMar Early Responder Maint OD';
define isv_od_log_dr/display 'LogMar Delayed Responder OD';
define isv_od_log_nr/display 'LogMar Non-Responder OD';
define isv_od_log_nc/display 'LogMar Not Considered OD';
define isv_od_ph_mr/display 'PH/MR OD';
define isv_od_fa_kg_comments/display 'FA OD KG Comments';
define isv_od_oct_cmt/display 'OCT CMT OD';
define isv_od_oct_kg/display 'OCT OD KG Comments' ;
define isv_od_rott/display 'ROTTERDAM STAGE 0 4 OD';
define isv_od_stage_comments/display 'OD Stage Comments' ;
define isv_od_er_nomaint/display 'Early Responder No Maint OD';
define isv_od_er_maint/display 'Early Responder Maint OD';
define isv_od_dr/display 'Delayed Responder OD';
define isv_od_nr/display 'Nonresponder OD';
define isv_od_nc/display 'Not Considered OD';
define isv_os_va/display 'VA OS';
define isv_os_va_logmar/display 'VA OS LogMar';
define isv_os_first_logmar/display 'First OS LogMar';
define isv_os_logmar_diff/display noprint;
define calc_os_logmar_diff_formatted/computed 'Logmar OS Diff';
define isv_os_treatment/display 'Treatment OS';
define isv_os_logmar_er_nomaint/display 'LogMar Early Responder NoMaint OS';
define isv_os_logmar_er_maint/display 'LogMar Early Responder Maint OS';
define isv_os_logmar_dr/display 'LogMar Delayed Responder OS';
define isv_os_logmar_nr/display 'LogMar Non-Responder OS';
define isv_os_logmar_nc/display 'LogMar Not Considered OS';
define isv_os_ph_mr/display 'PH/MR OS';
define isv_os_fa_kg_comments/display 'FA OS KG Comments' ;
define isv_os_oct_cmt/display 'OCT CMT OS';
define isv_os_oct_kg_comments/display 'OCT OS KG Comments' ;
define isv_os_rott/display 'ROTTERDAM STAGE 0 4 OS';
define isv_os_stage_comments/display 'OS Stage Comments';
define isv_os_er_nomaint/display 'Early Responder No Maint OS';
define isv_os_er_maint/display 'Early Responder Maint OS';
define isv_os_dr/display 'Delayed Responder OS';
define isv_os_nr/display 'Non-Responder OS';
define isv_os_nc/display 'Not Considered OS';
define isv_medical_information/display 'UPDATED MEDICAL INFORMATION';

compute calc_od_logmar_diff_formatted;
if isv_od_logmar_diff = . or
(isv_od_logmar_diff >= -.1 and isv_od_logmar_diff <=.2) then
calc_od_logmar_diff_formatted= isv_od_logmar_diff;
else if isv_od_logmar_diff > .2 then
do;
calc_od_logmar_diff_formatted= isv_od_logmar_diff;
call define(_col_,'style',
'style=[foreground=black
background=green
font_weight=bold]');
end;
else if isv_od_logmar_diff < -.1 then
do;
calc_od_logmar_diff_formatted= isv_od_logmar_diff;
call define(_col_,'style',
'style=[foreground=black
background=red
font_weight=bold]');
end;
endcomp;
compute calc_os_logmar_diff_formatted;
if isv_os_logmar_diff = . or
(isv_os_logmar_diff >= -.1 and isv_os_logmar_diff <=.2) then
calc_os_logmar_diff_formatted= isv_os_logmar_diff;
else if isv_os_logmar_diff > .2 then
do;
calc_os_logmar_diff_formatted= isv_os_logmar_diff;
call define(_col_,'style',
'style=[foreground=black
background=green
font_weight=bold]');
end;
else if isv_os_logmar_diff < -.1 then
do;
calc_os_logmar_diff_formatted= isv_os_logmar_diff;
call define(_col_,'style',
'style=[foreground=black
background=red
font_weight=bold]');
end;
endcomp;

run;

%End ;

ods tagsets.excelxp close;
ods listing;
%mend;

%do_calls_report;



----- Original Message -----
From: Dana M.
To: SAS-L@LISTSERV.UGA.EDU
Sent: Tuesday, December 02, 2008 2:13 PM
Subject: Creating Multiple-Sheet Excel Workbooks with SAS ODS


Hi all,

I am new to SAS and ODS so bare with me.  I am having trouble creating
Multi-sheet Excel Workbooks with SAS ODS (SAS 9.1 and Excel 2003).  I
changed my tagsets, to:

ods tagsets.ExcelXP

chose a style, changed my file a .xls, ect and ran SAS.  What I ended
up with was an ugly excel file that did not include my titles or
footers and also split my tables among more tabs than I would have
chosen.  I tried to include the titles, footers, and rearrange the
tabs with the 'options' tagset code in this general format:

ods tagsets.ExcelXP options(embedded_titles='yes'
suppress_bylines='yes');
ods tagsets.ExcelXP options(sheet_name="First Sheet");

but my log recognized 'options' as a syntax error.  I looked for
online sources to move past this problem but they all show 'options'
as the code to change the format of the tagset.

Does anyone have any knowledge in this area they would be willing to
share or any links to online resources that could help me move past
this?

~Dana