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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.