PROC EXPORT excel formatting in SAS 9.2 on x64
in [SAS]
|
From: Ida on 14 Dec 2009 09:27 Hi, I have just switched from 9.1.3 on x32 to 9.2 on x64, and now my exports are getting lost in translation. I have created a code to run a bunch of analyses/queries and then export these results to a formatted excel workbook with multiple sheets. Each of 20 sheets has conditional formatting (e.g., if p<0.05, highlight in yellow). Previously, this excel formatting was upheld no matter how many times the sheets were overwritten with new data. Now, however, the sheets are overwritten and the excel formatting is lost. Previous code: PROC EXPORT data=file OUTFILE=file.xls DBMS=EXCEL REPLACE; SHEET="mysheet"; run; New code: same w/ DBMS=XLS instead of EXCEL I have tried all sorts of other DBMS and VERSIONS, with no success (some won't run, others won't format, etc...) I will have to switch back to my x32 w/9.1.3 if this can't be resolved, which I'd really rather not do. Any help would be greatly appreciated! Thank you in advance!
From: Joe Matise on 14 Dec 2009 14:40 Why did you change the DBMS to XLS from EXCEL? That's the problem. XLS is a different engine entirely, and does not work in the same manner as EXCEL. -Joe On Mon, Dec 14, 2009 at 8:27 AM, Ida <ihatoum(a)hsph.harvard.edu> wrote: > Hi, > > I have just switched from 9.1.3 on x32 to 9.2 on x64, and now my > exports are getting lost in translation. > > I have created a code to run a bunch of analyses/queries and then > export these results to a formatted excel workbook with multiple > sheets. Each of 20 sheets has conditional formatting (e.g., if > p<0.05, highlight in yellow). Previously, this excel formatting was > upheld no matter how many times the sheets were overwritten with new > data. Now, however, the sheets are overwritten and the excel > formatting is lost. > > Previous code: > PROC EXPORT data=file > OUTFILE=file.xls > DBMS=EXCEL REPLACE; > SHEET="mysheet"; > run; > > New code: > same w/ DBMS=XLS instead of EXCEL > > I have tried all sorts of other DBMS and VERSIONS, with no success > (some won't run, others won't format, etc...) I will have to switch > back to my x32 w/9.1.3 if this can't be resolved, which I'd really > rather not do. > > Any help would be greatly appreciated! > > Thank you in advance! >
From: Ida on 14 Dec 2009 16:01 Hi Joe, Thanks for the response. Unfortunately, EXCEL can't be used with x64: http://support.sas.com/kb/33/228.html I also tried plugging in things like EXCEL5 but that doesn't work either. Any other ideas? Thanks again! Ida On Dec 14, 2:40 pm, snoopy...(a)GMAIL.COM (Joe Matise) wrote: > Why did you change the DBMS to XLS from EXCEL? That's the problem. XLS is a > different engine entirely, and does not work in the same manner as EXCEL. > > -Joe > > > > On Mon, Dec 14, 2009 at 8:27 AM, Ida <ihat...(a)hsph.harvard.edu> wrote: > > Hi, > > > I have just switched from 9.1.3 on x32 to 9.2 on x64, and now my > > exports are getting lost in translation. > > > I have created a code to run a bunch of analyses/queries and then > > export these results to a formatted excel workbook with multiple > > sheets. Each of 20 sheets has conditional formatting (e.g., if > > p<0.05, highlight in yellow). Previously, this excel formatting was > > upheld no matter how many times the sheets were overwritten with new > > data. Now, however, the sheets are overwritten and the excel > > formatting is lost. > > > Previous code: > > PROC EXPORT data=file > > OUTFILE=file.xls > > DBMS=EXCEL REPLACE; > > SHEET="mysheet"; > > run; > > > New code: > > same w/ DBMS=XLS instead of EXCEL > > > I have tried all sorts of other DBMS and VERSIONS, with no success > > (some won't run, others won't format, etc...) I will have to switch > > back to my x32 w/9.1.3 if this can't be resolved, which I'd really > > rather not do. > > > Any help would be greatly appreciated! > > > Thank you in advance!
From: Joe Matise on 14 Dec 2009 18:05 I think you are out of luck then as far as proper imports go - XLS doesn't seem to work that way. I'd either use the 32 bit version of SAS, or see if you can replicate the same functionality with ODS. A third option is to use a template in excel, and use a VBA macro to copy over the formats to the SAS produced sheet, though that's of course at least slightly manual [though you could use DDE or VBScript to automate that part]. -Joe On Mon, Dec 14, 2009 at 3:01 PM, Ida <ijhatoum(a)gmail.com> wrote: > Hi Joe, > > Thanks for the response. Unfortunately, EXCEL can't be used with x64: > > http://support.sas.com/kb/33/228.html > > I also tried plugging in things like EXCEL5 but that doesn't work > either. > > Any other ideas? > > Thanks again! > > Ida > > > On Dec 14, 2:40 pm, snoopy...(a)GMAIL.COM (Joe Matise) wrote: > > Why did you change the DBMS to XLS from EXCEL? That's the problem. XLS > is a > > different engine entirely, and does not work in the same manner as EXCEL. > > > > -Joe > > > > > > > > On Mon, Dec 14, 2009 at 8:27 AM, Ida <ihat...(a)hsph.harvard.edu> wrote: > > > Hi, > > > > > I have just switched from 9.1.3 on x32 to 9.2 on x64, and now my > > > exports are getting lost in translation. > > > > > I have created a code to run a bunch of analyses/queries and then > > > export these results to a formatted excel workbook with multiple > > > sheets. Each of 20 sheets has conditional formatting (e.g., if > > > p<0.05, highlight in yellow). Previously, this excel formatting was > > > upheld no matter how many times the sheets were overwritten with new > > > data. Now, however, the sheets are overwritten and the excel > > > formatting is lost. > > > > > Previous code: > > > PROC EXPORT data=file > > > OUTFILE=file.xls > > > DBMS=EXCEL REPLACE; > > > SHEET="mysheet"; > > > run; > > > > > New code: > > > same w/ DBMS=XLS instead of EXCEL > > > > > I have tried all sorts of other DBMS and VERSIONS, with no success > > > (some won't run, others won't format, etc...) I will have to switch > > > back to my x32 w/9.1.3 if this can't be resolved, which I'd really > > > rather not do. > > > > > Any help would be greatly appreciated! > > > > > Thank you in advance! >
From: Mary on 14 Dec 2009 18:44 It sounds like you can't do what you are trying to do. One solution might be to make both the conditional formatting and the export part of SAS; you can do conditional traffic-light formatting from Proc Report; you'd have to use Excel tagsets around the proc report to get this to work, as Excel tagsets can handle the multiple sheets and also apply fonts, etc., to get your entire report done out of SAS with very little touch-up needed over in Excel. Another solution might be to make the conditional formatting an Excel Visual Basic program, then call the Visual Basic program after you do the export (this is more tricky to get to work totally from SAS than is the above approach). -Mary --- ihatoum(a)HSPH.HARVARD.EDU wrote: From: Ida <ihatoum(a)HSPH.HARVARD.EDU> To: SAS-L(a)LISTSERV.UGA.EDU Subject: PROC EXPORT excel formatting in SAS 9.2 on x64 Date: Mon, 14 Dec 2009 06:27:08 -0800 Hi, I have just switched from 9.1.3 on x32 to 9.2 on x64, and now my exports are getting lost in translation. I have created a code to run a bunch of analyses/queries and then export these results to a formatted excel workbook with multiple sheets. Each of 20 sheets has conditional formatting (e.g., if p<0.05, highlight in yellow). Previously, this excel formatting was upheld no matter how many times the sheets were overwritten with new data. Now, however, the sheets are overwritten and the excel formatting is lost. Previous code: PROC EXPORT data=file OUTFILE=file.xls DBMS=EXCEL REPLACE; SHEET="mysheet"; run; New code: same w/ DBMS=XLS instead of EXCEL I have tried all sorts of other DBMS and VERSIONS, with no success (some won't run, others won't format, etc...) I will have to switch back to my x32 w/9.1.3 if this can't be resolved, which I'd really rather not do. Any help would be greatly appreciated! Thank you in advance!
|
Next
|
Last
Pages: 1 2 Prev: New SAS tools for importing all of the sheets in a workbook or Next: SAS dynamic arrays |