Math Forum / Math Software / SAS / October 2008

This example may be helpful.  That are many other methods.

filename FT33F001 '?CSIDL_PERSONAL';
libname xl excel "%sysfunc(pathname(FT33F001))\4tab.xls";
data a b c d;
  set sashelp.class;
  run;
proc copy in=work  out=XL;
  select a b c d;
  run;
  quit;
proc contents data=XL._all_;
  run;
libname XL clear;
filename FT33F001 clear;

On Oct 29, 2:58 pm, "he.te...@gmail.com" <he.te...@gmail.com> wrote:

Proc compare base= a compare b;
id var1;
run;
Proc compare base= a compare c;
id var1;
run;
Proc compare base= a compare d;
id var1;
run;

Regards
Reddy
SAS BI Developer
info.sasreddy@gmail.com
INDIA

On Oct 29, 5:58 pm, "he.te...@gmail.com" <he.te...@gmail.com> wrote:

To make is clear. I have 4 sas datasets. they have the same variables,
but the variables have different values. I want to ouput the four
datasets into one excel file that has 4 tabs.

Thanks

Excel Tagsets can do this by changing the sheetname each time.  Here's a
macro I'm currently using to create a tab
for each study number; note especially that I'm changing the sheet by the
code
ods tagsets.excelxp options(sheet_name="&sheetname").   I'm quite pleased
with the results, as I have over
200 patients and 10,000 records in this report.

-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: he.terry@gmail.com
To: SAS-L@LISTSERV.UGA.EDU
Sent: Wednesday, October 29, 2008 7:29 AM
Subject: Re: how to put several sas datasets into several tab

On Oct 29, 5:58 pm, "he.te...@gmail.com" <he.te...@gmail.com> wrote:

To make is clear. I have 4 sas datasets. they have the same variables,
but the variables have different values. I want to ouput the four
datasets into one excel file that has 4 tabs.

Thanks

Method 1:

libname wrkbk excel 'c:\test.xls';

proc copy in = work out = wrkbk;
select A B C D;
run;

libname wrkbk clear;

Method 2:
%macro expt (table =);
proc export data = &table
    outfile = 'c:\test2.xls'
    dbms = excel replace;
    sheet = "&table";
run;
%mend expt;
%expt (table = A)
%expt (table = B)
%expt (table = C)
%expt (table = D)

What is a tab?  Are the four data sets SAS data sets or are they files
you want to input to SAS data sets.

-----Original Message-----
From: he.terry
Sent: Wednesday, October 29, 2008 2:58 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: how to put several sas datasets into several tab

Suppose I have 4 datasets "A","B","C","D" which have the same variables.
I want to output them into 4 tabs by SAS. How to do this?

Barry,

I implemented multisheet Excel reports following this SUGI 2007 paper.
Let me know if you run into any issues. All the details are in there.
The test case used there is a single dataset and a column value is used
to define separate TABs in Excel. But you can look through the helpful
comments in the macro used and customise it easily enough.

Author: Vincent DelGobbo, SAS Institute Inc., Cary, NC
Title: Creating Multi-Sheet Excel Workbooks the Easy Way with SAS

Shekhar Jindal  | A c x i o m  F S G - V
501-252-0538 office |  501-252-0309 fax
Little Rock, AR 72223 | USA | www.acxiom.com
Yahoo IM - shekhar_jindal

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Schwarz, Barry A
Sent: Friday, October 31, 2008 6:39 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: [SAS-L] how to put several sas datasets into several tab

What is a tab?  Are the four data sets SAS data sets or are they files
you want to input to SAS data sets.

-----Original Message-----
From: he.terry
Sent: Wednesday, October 29, 2008 2:58 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: how to put several sas datasets into several tab

Suppose I have 4 datasets "A","B","C","D" which have the same variables.
I want to output them into 4 tabs by SAS. How to do this?
*************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank you.
*************************************************************************