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

I am trying to import an xslx data file.  There are several fields that have date/time values, but when SAS imports, they are converted to just the date value.  There are no blank values or other data in the columns that would encourage SAS to make up a different format.  I cannot figure out how to force it to read the full date and time.  I have played around with DBDSOPTS, but I don't think I fully understand how that works.  My log is coming out clean.  I have attached a sample file for you to play with.  Can someone help?   Data in Excel: Data in SAS after import:   Here is the code I am attempting to use:   filename testrpt "C:\Test Report.xlsx"; proc import out=have datafile=testrpt dbms=excelcs replace; range="Export$"; scantext=yes; usedate=yes; scantime=yes; run;     ... View more

From my extensive Google searching, I have not been able to find a solution to this problem.  I am wondering if what I want to do is possible in SAS.   I need to create a rather large graph.  I basically have 3 grouping variables.  The first is number of variants (0 or 1).  This variable determines the bars.  The second group is investigator site.  This clusters the bars together.  The third is country.  This is a panelby variable.  Below is the code that I am using (sample data attached).   ods graphics / reset width=10in height=4in; proc sgpanel data=site; /* where country in ('BR','CO'); */ panelby country / onepanel layout=columnlattice colheaderpos=bottom noborder novarname; vbar siteid / group=nvariants groupdisplay=cluster grouporder=data; rowaxis display=(nolabel); colaxis display=(nolabel); run;   I am running into 2 issues: When a group is not available for the group variable (NVARIANTS), it compresses the empty spaces and centers the bars.  I want the zero space left blank.  Notice how the tick mark for Brazil sites 016 and 018 is inbetween the 2 bars, but for sites 005 and 015, it compresses the empty bar and centers the remaining bar (no space for a zero count bar).  From my understanding, there is no way to make this happen in SAS. The panelby variable (COUNTRY) creates a space for every site even if it's not available in that country.  Notice that Brazil has an empty space for sites 395 and 396.  Also, notice that Colombia is displaying several empty sites (because these are from Brazil and not Colombia).  This is basically what I want to have happen in my first issue, but I don't want it here.  I want to only display sites applicable to the associated country. Subgroup of my graph:   Basically, I want to swap what is happening in both issues.  I want NVARIANTS to create a spot for each possibilitiy, even if it's not there, but I want it to NOT create a spot for each site unless it is found in that country.   This is what I get on my full data (very cluttered):     But this is what I want the graph to look like (generated manually in Excel, butI have been asked to automate in SAS):   ... View more

I have an unusual task where I am given 3 lists that I merge together, and I am supposed to only report a single list of all distinct values found in all lists.  Below is some code that I made up to illustrate the idea.   proc sql; create table test (ID char(3), LIST1 char(10), LIST2 char(10), LIST3 char(10)); insert into test (id, list1, list2, list3) values ('001', 'A,B,C', 'A,B,C', 'A,B,C') values ('002', 'A,C,D', 'C', 'A,C,D') values ('003', 'A,B,C,D,E', '', '') values ('004', 'A,B,C,D,E', 'A,B', 'C,D,F'); quit; I am wondering if there is a simple way to end up with the following results: ID LIST 001 A,B,C 002 A,C,D 003 A,B,C,D,E 004 A,B,C,D,E,F   The best I can think is to parse each item from each list into a separate variable, transpose from horizontal to vertical, remove any duplicate values, transpose from vertical to horizontal, then CATX everything again into a single list.  Please tell me there is better way! ... View more