output an Excel file with different tabs in a form

[Hoibai]

Hello everyone,

how can I output an Excel file with different tabs and with data in the cells  in SAS? Thank you.

For example:

[PaigeMiller]

We need to see (a portion of) the actual SAS data set you will use, otherwise this is impossible to answer. Please show us the SAS data set you are using as working SAS data step code (examples and instructions). Do NOT show us the SAS data set in an Excel file; do NOT show us data as copy and pasted from Excel; do NOT show us the SAS data set in any other way, except as shown at the link.

[SASKiwi]

Filling specific cells in an Excel form directly from SAS is not usually possible. A good solution is to have SAS populate a tab of just columns and rows, then have an Excel macro that copies the required data to the right form cells.

You can use PROC EXPORT to populate a tab with column and rows from a SAS dataset. Then the Excel macro is run fill out the pre-designed form.

[Ksharp · Accepted solution]

You want this ?

data table3way;
  length dmtype durationbi $35;
  infile datalines dlm=',' dsd;
  input dmtype $ ordvar Durationbi $ Num cMean cSTD LowerCL UpperCL Prob_t;
return;
datalines;
"Type 1",1,"<=10 years",17,76.76,17.85,67.59,85.94,0.7243
"Type 1",2,">10 year",44,75.30,13.06,71.32,79.27,0.7243
"Type 2 (*ESC*)n weqwqrqwer",1,"<=10 years",47,67.23,22.22,44.44,88.88,0.5584
"Type 2 (*ESC*)n weqwqrqwer",2,">10 year",35,69.43,33.33,55.55,99.99,0.5584
;
run;

ods excel file="c:\temp\spantest.xlsx"   ;

title 'How to span Header Rows amd Data Rpws with RWI and DATA step';
data _null_; 
  set table3way end=last; 
  by dmtype;
  if _N_ = 1 then do; 
      dcl odsout obj(); 
      obj.table_start(); 
      obj.head_start(); 
	  ** Header row 1;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Type ", row_span:2, column_span: 1, style_attr:"vjust=m color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "Results", column_span:7, style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.row_end(); 
	  ** Header row 2;
      obj.row_start(type: "Header"); 
      obj.format_cell(text: "Durationbi", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "num", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "cmean", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "cstd", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "lowercl",style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "uppercl", style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.format_cell(text: "probt",style_attr:"color=black backgroundcolor=cxdddddd fontweight=bold"); 
      obj.row_end(); 
      obj.head_end(); 
    end;
  ** row for every obs;
  ** treat dmtype and prob_t differently so they span rows;

      obj.row_start(); 
	  if first.dmtype then do;
         obj.format_cell(data: dmtype,row_span:2, style_attr:"vjust=m fontweight=bold" ); 
	  end;
      obj.format_cell(data: Durationbi, row_span:1); 
      obj.format_cell(data: num, row_span:1); 
      obj.format_cell(data: cmean, row_span:1); 
      obj.format_cell(data: cstd, row_span:1); 
      obj.format_cell(data: lowercl, row_span:1); 
      obj.format_cell(data: uppercl, row_span:1); 
	  if first.dmtype then do;
         obj.format_cell(data: prob_t, row_span:2, style_attr:"vjust=m fontweight=bold"); 
	  end;
      obj.row_end(); 
   
  if last then do; 
      obj.table_end(); 
    end; 
run;
footnote;title;
ods excel close;