Hi,
I'm fairly new to using ODS and I find it pretty difficult to use when it comes to creating a nice report with the tagset ExcelXp...
The table below is written in an email but I would also like it to be written in an excel document.
I'm trying to output the following table into Excel with a similar style:
HEADING1 | HEADING2 | HEADING3 | |||||||||
Field1 | Field2 | Field3 | Field4 | Field5 | Field6 | Field7 | Field8 | Field9 | Field10 | Field11 | Field12 |
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
The trick is that the content of the last <TR> should come from a SAS table so at the moment this is what i'm doing:
ods _all_ close;
ODS listing close;
ods tagsets.Excelxp file="file.xls" style=SASWeb;
ods noresults;
ods tagsets.Excelxp options(sheet_interval="none"
Embedded_titles='yes'
absolute_column_width = "12"
sheet_name='TEST');
Title JUSTIFY= LEFT 'Title';
proc report data=MATCH nowd;
columns var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 var12;
define var1 / 'Field1' ;
define var2 / 'Field2' ;
define var3 / 'Field3' ;
define var4 / 'Field4' ;
define var5 / 'Field5' ;
define var6 / 'Field6' ;
define var7 / 'Field7' ;
define var8 / 'Field8' ;
define var9 / 'Field9' ;
define var10 / 'Field10' ;
define var11 / 'Field11' ;
define var12 / 'Field12' ;
run;
How can I get ODS to also print the HEADINGS, the field names and the values in this format in an Excel file ?
This was the solution
proc report data=MATCH nowd style(Header)=[just=center];
columns ('HEADING1' var1 var2 var3 var4) ('HEADING2' var5 var6 var7) ('HEADING3' var8 var9 var10 var11 var12);
define var1 / 'Field1' group;
define var2 / 'Field2' group;
define var3 / 'Field3' group;
define var4 / 'Field4' group;
define var5 / 'Field5' group;
define var6 / 'Field6' group ;
define var7 / 'Field7' group ;
define var8 / 'Field8' group;
define var9 / 'Field9' group;
define var10 / 'Field10' group;
define var11 / 'Field11' group;
define var12 / 'Field12' group;
run;
This was the solution
proc report data=MATCH nowd style(Header)=[just=center];
columns ('HEADING1' var1 var2 var3 var4) ('HEADING2' var5 var6 var7) ('HEADING3' var8 var9 var10 var11 var12);
define var1 / 'Field1' group;
define var2 / 'Field2' group;
define var3 / 'Field3' group;
define var4 / 'Field4' group;
define var5 / 'Field5' group;
define var6 / 'Field6' group ;
define var7 / 'Field7' group ;
define var8 / 'Field8' group;
define var9 / 'Field9' group;
define var10 / 'Field10' group;
define var11 / 'Field11' group;
define var12 / 'Field12' group;
run;
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.