My company creates xls output using ODS TAGSETS.EXCELXP.  We want to export directly to xlsx using ODS EXCEL, but we have some limitations.  We were excited when we finally upgraded from SAS 9.4 M3 to M6 thinking those limitations would be resolved, but not all were.  We still have a lingering issue.  In ODS TAGSETS, we can apply the AUTOFIT_HEIGHT='yes' option to make the row heights adjust based on the text.  That option seems to have disappeared in ODS EXCEL.  I know ODS EXCEL has ROW_HEIGHT and ABSOLUTE_ROW_HEIGHT, but they do not have the same functionality.  I can use these to either speicfy the same height for all rows, or I can speicify the height of each row individually.  This becomes ridiculously unfeasible when dealing with 1000s of records.  This is a bit of a deal breaker because our text wraps, and we don't want wrapped text to be hidden and missed by a shortened row.   This is example code of how we use ODS TAGSETS, and we want to replicate this output with ODS EXCEL:   /*** Template intended for xls output ***/ ODS PATH work.templat(update) sasuser.templat(read) sashelp.tmplmst(read); proc template; define style work.newxls; parent=styles.normal; style default / fontsize = 14pt; style Data from Column / bordertopwidth = 0px borderrightstyle = solid bordertopcolor = #AAC1D9 padding = 3 borderbottomcolor = #AAC1D9 borderbottomwidth = 1px borderbottomstyle = solid fontweight = medium borderrightwidth = 1px verticalalign = _undef_ fontfamily = "<sans-serif>, <MTsans-serif>, Helvetica, sans-serif" borderleftstyle = solid borderrightcolor = #AAC1D9 borderleftcolor = #AAC1D9 textalign = _undef_ fontsize = 10pt borderleftwidth = 0px bordertopstyle = solid; style Header from Column / bordertopwidth = 0px borderrightstyle = solid bordertopcolor = #AAC1D9 padding = 3 borderbottomcolor = #AAC1D9 backgroundcolor = #faf3d4 borderbottomwidth = 1px borderbottomstyle = solid fontweight = bold borderrightwidth = 1px verticalalign = middle fontfamily = "<sans-serif>, <MTsans-serif>, sans-serif" borderleftstyle = solid borderrightcolor = #AAC1D9 borderleftwidth = 0px borderleftcolor = #AAC1D9 textalign = center fontsize = 12pt color = cx000000 bordertopstyle = solid; end; run; /*** This code will use the NEWXLS template to generate xls output and properly uses AUTOFIT_HEIGHT and WRAPTEXT. ***/ %let ods_options=%str( row_repeat = 'header' autofilter = 'yes' orientation = 'landscape' frozen_headers='5' center_horizontal = 'yes' fittopage = 'yes' pages_fitwidth = '1' pages_fitheight = '100' autofit_height = 'yes' embedded_titles = 'yes' embedded_footnotes = 'yes' wraptext = 'yes' gridlines = 'yes' sheet_interval = 'none'); ods listing close; ods tagsets.excelxp file="c:\temp\Test_Output.xls" style=work.newxls; title1 j=l "Vertex Pharmaceuticals Incorporated"; title2 j=l "Protocol: "; title3 j=l "Test Report Demonstraiting Row Height - Written for SAS 9.4 M3"; ods tagsets.excelxp options (&ods_options. sheet_name='Test Case' absolute_column_width='12'); proc report data=sashelp.aacomp nowindows split='^'; column locale key lineno text; define locale / 'Short Variable' style(column)={tagattr='format:@'}; define key / 'Medium Variable' style(column)={tagattr='format:@'}; define lineno / 'Short Variable' style(column)={tagattr='format:@'}; define text / 'Long Variable Text with a Long Label to Illustrate Wrapping' style(column)={tagattr='format:@'}; run; ods tagsets.excelxp close; ods listing;   This is example code of how we would use ODS EXCEL, but  we get can't find a replacement for AUTOFIT_HEIGHT.  Notice how wrapped text is hidden from view:     /*** Template intended for direct xlsx output ***/ ODS PATH work.templat(update) sasuser.templat(read) sashelp.tmplmst(read); proc template; define style work.odsexcel; parent=styles.excel; class systemtitle/ fontsize=12pt color=black; class systemfooter/ color=black; class header/ fontsize=12pt fontweight=bold verticalalign=middle textalign=center color=black backgroundcolor=#faf3d4; class data/ fontsize=10pt verticalalign=_undef_ textalign=_undef_; end; run; /*** This code will use the ODSEXCEL template to generate xlsx output but does not allow the same options. WRAPTEXT is no longer an option. It produces a warning. But it can be replaced with the FLOW option. AUTOFIT_HEIGHT is no longer an option. It produces a warning. I can't find an equivalent option. ***/ %let ods_options=%str( row_repeat = 'header' autofilter = 'yes' orientation = 'landscape' frozen_headers='5' center_horizontal = 'yes' fittopage = 'yes' pages_fitwidth = '1' pages_fitheight = '100' /* autofit_height = 'yes'*/ embedded_titles = 'yes' embedded_footnotes = 'yes' /* wraptext = 'yes'*/ flow='tables' gridlines = 'yes' sheet_interval = 'none'); ods listing close; ods excel file="c:\temp\Test_Output.xlsx" style=work.odsexcel; title1 j=l "Vertex Pharmaceuticals Incorporated"; title2 j=l "Protocol: "; title3 j=l "Test Report Demonstraiting Row Height - Written for SAS 9.4 M6"; ods excel options (&ods_options. sheet_name='Test Case' absolute_column_width='12'); proc report data=sashelp.aacomp nowindows split='^'; column locale key lineno text; define locale / 'Short Variable' style(column)={tagattr='format:@'}; define key / 'Medium Variable' style(column)={tagattr='format:@'}; define lineno / 'Short Variable' style(column)={tagattr='format:@'}; define text / 'Long Variable Text with a Long Label to Illustrate Wrapping' style(column)={tagattr='format:@'}; run; ods excel close; ods listing;     Please let me know how I can escalate this to be fixed.  We are running into issues where xls files are too large to email, and we don't want to have to manually save them as xlsx files. ... View more

Hi   Need help to Highlight mismatched rows(compare 2-3 , 4-5,6-7 rows so on dynamically) data Using SAS while generating a multi sheet excel file . SAS is on Unix server. I am using SAS EG 7.1 Below is the data I have to compare every two rows and highlight the mismatched data while generating the Excel and send it via email. Note - there are multiple sheets and columns vary (sometimes there maybe 10 columns, sometimes it may be 18)   I have SAS code(used ODS) to generate excel with multiple sheets and send it via email and separate a VBA macro to highlight the mismatched data which I run manually. But I am unable to integrate both in to single code so that no manual work is needed, I really appreciate if anyone can suggest any way to do it in one single code.   Below is sample dataset , rows and column are dynamically created based on the project. Need to compare 2-3,4-5, 6-7..so-on rows and highlight(I have given blue to the font but i need whole box as yellow) only when they don’t match.   ssn firstname lastname empid address city state zip 000-11-1234 abc d 1001 plotno 123 abcd rd 12345 000-11-1234 abc e 1001 plotno 123 abcd rd 12345 111-12-3456 efgh f 2001 house 897 tyui fg 23456-789 111-12-3456 efgh f 2001 hs 897 tyui fg 23456 444-56-7890 wrt y 3001     xc 12345 444-56-7890 wert y 3002     xc 12345   my VBA code:   Sub compare_Auto() Dim LastColumn As Long LastColumn = ActiveSheet.Range("A1").CurrentRegion.Columns.Count Dim rng As Range, rngDiff As Range Set rng = Range(Cells(2, 7), Cells(3, LastColumn)) Do While Application.CountA(rng) > 0  'loop while have content         Set rngDiff = Nothing   'reset range         On Error Resume Next    'ignore "No cells were found" error if no differences         Set rngDiff = rng.ColumnDifferences(Comparison:=rng.Cells(1))         On Error GoTo 0         'stop ignoring errors         If Not rngDiff Is Nothing Then             rngDiff.Interior.ColorIndex = 6 'color all differences             rngDiff.Offset(-1).Interior.ColorIndex = 6         End If         Set rng = rng.Offset(2) 'two rows down     Loop End Sub ... View more

Hi. I'm SupermanJP.   I updated SAS plotter,  modern data visualization package for SAS base. https://github.com/Superman-jp/SAS_Plotter    document https://superman-jp.github.io/SAS_Plotter/    new features    new plots " MultiHistogram" was available! multihitogram is histogram created by each category variable and pair variable. Box width of histogram is reflect the response variable. multihistogram is used for frequency comparison of multiple category in small display area.   This macro was designed based on the report of Wierenga, Madison R et al. (https://www.ncbi.nlm.nih.gov/pmc/articles/PMC8140601/.)   official mail address: sasplotter@picolabs.jp official web site (Japanese) https://picolabs.jp Please feel free to contact me if you have any bug reports, feedback, or requests. ... View more

Hi all,   I have 4 datasets as below and requirement for the table want are : - Ignore col1 values which are not common for all 4 tables - join all col1 col2 col3 col4 values in the want table to show all different values for col2 in each table.   Table 1 col1 col2 1 yes 2 yes 3 yes 4 yes 5     Table 2  col1 col2 1 yes 2 no1 3 yes 4 no1   Table 3 col1 col2 1 yes 2 no2 3 yes 4 no2   Table 4 col1 col2 1 yes 2 no3 3 yes 4 no3   table want col1 col2 col3 col4 col5 1 yes yes yes yes 2 yes no1 no2 no3 3 yes yes yes yes 4 yes no1 no2 no3   Thanks in advance kajal ... View more