1)Is there a way  that I could directly create Pivot table from sas instead of creating a HTML file with a " Create Pivottable " button on it. The reason i ask is that I need to export the SAS data into a Pivot in Unix and upload it at a certain location. Also, the data I am dealing with is huge and opening a HTML file is taking a lot of time , let alone the time it takes to create the Pivot Table 2)Second question: Is there any option that can be used to just retain the excel tab with Pivot table and delete the rest of the sheets ( including data sheet). Thanks ... View more

HI, I am trying to sort a very large dataset( aprrox 32G) on 4 variables. I tried using the Tagsort option but got the error meessage specifying ERROR: TAGSORT option cannot be used with engines that do not support random access. Any suggestions as to how can this error be avoided? Or how can I sort the dataset more efficiently. Thanks, Neha ... View more

Hi, I want to create a Excel report which would have data output from SAS dataset. I am using Proc report through ODS Tagset.ExcelXP to write the data in an Excel. I am facing a couple of issues while trying to complete this 1) Report contains 3 levels of Spanning headers and I would need different background colors for all the headers. I was trying the Traffic Lighting way proc format;      value $color           'REPORT' = '#9999FF'           'Australia' = '#FF6600'      other = '#FF6600'; run; ods listing close; ods tagsets.excelxp file = 'report.xls' options ( Autofilter = 'YES' absolute_column_width = "11,14,........) style = sansprinter; proc report data = sfin.reportfin nowd style(header) = { background = $color. font_size= 10pt}; column ('REPORT'('Australia' ABC DEF XYZ ('States' X Y Z (....)))); display ABC .... .. run; but the headers do not take the desired background values. Not sure if this appoach is correct. 2) Inline formatting using ods escapechar not working.Although I am not getting any errors in the log. 3) This report would be generated bimonthly and as part of the next iteration, I would want to open the same excel sheet and wrtie out the results. all this would happen dynamically, meaning I would not know how many rows would get populated in the spreadsheet.The challenge here is that I am not finding any option that allows me to open an existing spreadsheet to write out the results.Any suggestions? ... View more