output an Excel file with different tabs in a form

Hoibai — Fri, 07 Feb 2025 10:29:57 GMT

Hello everyone,

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

For example:

Re: output an Excel file with different tabs in a form

PaigeMiller — Fri, 07 Feb 2025 10:50:39 GMT

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.

Re: output an Excel file with different tabs in a form

SASKiwi — Fri, 07 Feb 2025 20:45:14 GMT

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.

Re: output an Excel file with different tabs in a form

Ksharp — Sat, 08 Feb 2025 07:19:04 GMT

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;

topic Re: output an Excel file with different tabs in a form in SAS Programming

output an Excel file with different tabs in a form

Re: output an Excel file with different tabs in a form

Re: output an Excel file with different tabs in a form

Re: output an Excel file with different tabs in a form