ODS Tagsets ExcelXP
in [SAS]
|
Prev: jackknife concept
Next: SYMGET
From: Jen on 9 May 2006 09:46 Hello Everyone, I am using ODS Tagsets ExcelXP to export several datasets into Excel mutiple worksheets. I specified the worksheets' name using the following code. However .xls file I gets does not show me the worksheets name according to what I specified Another question is that I cannot do further calculation on the negative values I export from my SAS datasets into Excel using ODS Tagsets ExcelXP. For example ( 1.74%) should be -1.74%, but excel shows (1.74%). $-3,027,863 should be -3027863, excel shows $-3027863. Excel does not allow me to do any other calculation based on the this result. So, I am wondering if my code is correct or there is any other way to fix that. I am not very familiar with ODS Tagsets ExcelXP. I saw that it has the function to import and export datasets into or from multiple Excel worksheets. I am wondering if it is better than SAS/ACCESS? Currently, we do not have SAS/ACCESS, so we cannot use proc import or proc export to load excel file using SAS. Thank you very much! ODS TAGSETS.EXCELXP OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME = 'Collision'); PROC PRINT DATA = US_COLL NOOBS LABEL; VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov YTD_Recov_Tar Recov_Plan PCTN_Recov&m PCTN_Recov_Tar YTD_PCTN_Recov PCTN_Recov_plan; TITLE1 "COLLISION RECOVERY REPORT"; RUN; ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME = 'Comprehensive'); PROC PRINT DATA = US_COMP NOOBS LABEL; VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov PCTN_Recov&m YTD_PCTN_Recov; TITLE1 "COMPREHENSIVE SUBROGATION RECOVERY REPORT"; RUN; ODS TAGSETS.EXCELXP CLOSE;
From: Peter Crawford on 9 May 2006 11:03 On Tue, 9 May 2006 06:46:50 -0700, Jen <leiliang77(a)GMAIL.COM> wrote: >Hello Everyone, > >I am using ODS Tagsets ExcelXP to export several datasets into Excel >mutiple worksheets. I specified the worksheets' name using the >following code. However .xls file I gets does not show me the >worksheets name according to what I specified > >Another question is that I cannot do further calculation on the >negative values I export from my SAS datasets into Excel using ODS >Tagsets ExcelXP. > >For example ( 1.74%) should be -1.74%, but excel shows (1.74%). > $-3,027,863 should be -3027863, excel shows $-3027863. Excel does not >allow me to do any other calculation based on the this result. So, I am >wondering if my code is correct or there is any other way to fix that. > >I am not very familiar with ODS Tagsets ExcelXP. I saw that it has the >function to import and export datasets into or from multiple Excel >worksheets. I am wondering if it is better than SAS/ACCESS? Currently, >we do not have SAS/ACCESS, so we cannot use proc import or proc export >to load excel file using SAS. > >Thank you very much! > > >ODS TAGSETS.EXCELXP OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME = >'Collision'); > PROC PRINT DATA = US_COLL NOOBS LABEL; > VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov > YTD_Recov_Tar Recov_Plan PCTN_Recov&m PCTN_Recov_Tar > YTD_PCTN_Recov PCTN_Recov_plan; > TITLE1 "COLLISION RECOVERY REPORT"; > > RUN; > ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME = 'Comprehensive'); > PROC PRINT DATA = US_COMP NOOBS LABEL; > VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov > PCTN_Recov&m YTD_PCTN_Recov; > TITLE1 "COMPREHENSIVE SUBROGATION RECOVERY REPORT"; > RUN; >ODS TAGSETS.EXCELXP CLOSE; sounds like an excel import problem.
From: Jack Hamilton on 4 May 2006 21:33 On 5/9/2006 6:46 AM Jen said the following: > Hello Everyone, > > I am using ODS Tagsets ExcelXP to export several datasets into Excel > mutiple worksheets. I specified the worksheets' name using the > following code. However .xls file I gets does not show me the > worksheets name according to what I specified What names are you getting? Are you using the latest version of the tagset? I know there have recently been changes in this area. The latest version prints this message in the log: NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.34, 04/07/06). Add options(doc='help') to the ods statement for more information. (The web page, <http://support.sas.com/rnd/base/topics/odsmarkup/, says that the tagset was updated in May; I on't know why the info line says April.) > Another question is that I cannot do further calculation on the > negative values I export from my SAS datasets into Excel using ODS > Tagsets ExcelXP. > > For example ( 1.74%) should be -1.74%, but excel shows (1.74%). > $-3,027,863 should be -3027863, excel shows $-3027863. Excel does not > allow me to do any other calculation based on the this result. So, I am > wondering if my code is correct or there is any other way to fix that. It's probably interpreting the numbers as strings, but it's hard to say why that might be. I use ExcelXP a lot in production programs, and I don't have this problem. You might play around with the formats of the variables in question. Also, there's a whole slew of tagset options dealing with how numbers are interpreted. Use the DOC='HELP' option to get a list. > I am not very familiar with ODS Tagsets ExcelXP. I saw that it has the > function to import and export datasets into or from multiple Excel > worksheets. No, it doesn't import or export to Excel. It creates an XML file (not a native format file) that Excel knows how to read. If you want to import data from an existing Excel workbook, the ExcelXP tagset won't help you. >I am wondering if it is better than SAS/ACCESS? Currently, > we do not have SAS/ACCESS, so we cannot use proc import or proc export > to load excel file using SAS. If you don't have SAS/Access, then the tagset is definitely a better choice. > Thank you very much! > > > ODS TAGSETS.EXCELXP OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME = > 'Collision'); > PROC PRINT DATA = US_COLL NOOBS LABEL; > VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov > YTD_Recov_Tar Recov_Plan PCTN_Recov&m PCTN_Recov_Tar > YTD_PCTN_Recov PCTN_Recov_plan; > TITLE1 "COLLISION RECOVERY REPORT"; > > RUN; > ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME = 'Comprehensive'); > PROC PRINT DATA = US_COMP NOOBS LABEL; > VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m YTD_Recov > PCTN_Recov&m YTD_PCTN_Recov; > TITLE1 "COMPREHENSIVE SUBROGATION RECOVERY REPORT"; > RUN; > ODS TAGSETS.EXCELXP CLOSE; >
From: yzg9 on 10 May 2006 11:07 Ah, I see a new version, May 10, 2006 is the date stamp on the file to download. Interesting issue though... I just ran this code: ODS tagsets.excelxp file="Y:\SAS Programs\Tagsets\ExcelXPHelpMay2006.xml" options(doc="help") ; ods tagsets.excelxp close; And the resulting file, "ExcelXPHelpMay2006.xml", cannot be read by Excel (error window pops up and says: "Unable to read file"). When I am able to get it open (via MSWord, showing the XML tags) there isn't any text besides the header information (unless the font color is white), just lots of border tags and paragraph symbols (placed there by Word) - no text. There is text in the LOG. John Gerstle, MS Biostatistician Northrop Grumman CDC Information Technological Support Contract (CITS) NCHSTP \DHAP \HICSB \Research, Analysis, and Evaluation Section Centers for Disease Control and Prevention "As far as the laws of mathematics refer to reality, they are not certain; and as far as they are certain, they do not refer to reality." Albert Einstein, addressing the Prussian Acadamy of Science, Berlin , Jan 27, 1921 "Boss. We've got cats." "Meow" >> -----Original Message----- >> From: owner-sas-l(a)listserv.uga.edu [mailto:owner-sas-l(a)listserv.uga.edu] >> On Behalf Of Jack Hamilton >> Sent: Thursday, May 04, 2006 9:33 PM >> To: Jen >> Cc: SAS-L(a)LISTSERV.UGA.EDU >> Subject: Re: [SAS-L] ODS Tagsets ExcelXP >> >> On 5/9/2006 6:46 AM Jen said the following: >> > Hello Everyone, >> > >> > I am using ODS Tagsets ExcelXP to export several datasets into Excel >> > mutiple worksheets. I specified the worksheets' name using the >> > following code. However .xls file I gets does not show me the >> > worksheets name according to what I specified >> >> What names are you getting? >> >> Are you using the latest version of the tagset? I know there have >> recently been changes in this area. The latest version prints this >> message in the log: >> >> NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.34, 04/07/06). Add >> options(doc='help') to the ods statement for more information. >> >> (The web page, <http://support.sas.com/rnd/base/topics/odsmarkup/, says >> that the tagset was updated in May; I on't know why the info line says >> April.) >> >> > Another question is that I cannot do further calculation on the >> > negative values I export from my SAS datasets into Excel using ODS >> > Tagsets ExcelXP. >> > >> > For example ( 1.74%) should be -1.74%, but excel shows (1.74%). >> > $-3,027,863 should be -3027863, excel shows $-3027863. Excel does not >> > allow me to do any other calculation based on the this result. So, I am >> > wondering if my code is correct or there is any other way to fix that. >> >> It's probably interpreting the numbers as strings, but it's hard to say >> why that might be. I use ExcelXP a lot in production programs, and I >> don't have this problem. You might play around with the formats of the >> variables in question. Also, there's a whole slew of tagset options >> dealing with how numbers are interpreted. Use the DOC='HELP' option to >> get a list. >> >> > I am not very familiar with ODS Tagsets ExcelXP. I saw that it has the >> > function to import and export datasets into or from multiple Excel >> > worksheets. >> >> No, it doesn't import or export to Excel. It creates an XML file (not a >> native format file) that Excel knows how to read. If you want to import >> data from an existing Excel workbook, the ExcelXP tagset won't help you. >> >> >I am wondering if it is better than SAS/ACCESS? Currently, >> > we do not have SAS/ACCESS, so we cannot use proc import or proc export >> > to load excel file using SAS. >> >> If you don't have SAS/Access, then the tagset is definitely a better >> choice. >> >> > Thank you very much! >> > >> > >> > ODS TAGSETS.EXCELXP OPTIONS(EMBEDDED_TITLES='YES' SHEET_NAME = >> > 'Collision'); >> > PROC PRINT DATA = US_COLL NOOBS LABEL; >> > VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m >> YTD_Recov >> > YTD_Recov_Tar Recov_Plan PCTN_Recov&m >> PCTN_Recov_Tar >> > YTD_PCTN_Recov PCTN_Recov_plan; >> > TITLE1 "COLLISION RECOVERY REPORT"; >> > >> > RUN; >> > ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME = 'Comprehensive'); >> > PROC PRINT DATA = US_COMP NOOBS LABEL; >> > VAR Reg State_N Loss1-Loss&m YTD_Loss Recov1-Recov&m >> YTD_Recov >> > PCTN_Recov&m YTD_PCTN_Recov; >> > TITLE1 "COMPREHENSIVE SUBROGATION RECOVERY >> REPORT"; >> > RUN; >> > ODS TAGSETS.EXCELXP CLOSE; >> >
From: Dianne Rhodes on 10 May 2006 11:30 > From: owner-sas-l(a)listserv.uga.edu > [mailto:owner-sas-l(a)listserv.uga.edu] On Behalf Of Gerstle, > John (CDC/NCHSTP/DHAP) (CTR) > > > Ah, I see a new version, May 10, 2006 is the date stamp on > the file to download. > > Interesting issue though... > > I just ran this code: > ODS tagsets.excelxp > file="Y:\SAS Programs\Tagsets\ExcelXPHelpMay2006.xml" > options(doc="help") ; > ods tagsets.excelxp close; > > And the resulting file, "ExcelXPHelpMay2006.xml", cannot be > read by Excel (error window pops up and says: "Unable to read file"). > > When I am able to get it open (via MSWord, showing the XML > tags) there isn't any text besides the header information > (unless the font color is white), just lots of border tags > and paragraph symbols (placed there by > Word) - no text. > > There is text in the LOG. > > What were you expecting to be in the file? The DOC option writes to the Log. You need a proc print or something that generates output to ODS. I've been doing this with proc tabulate, and I right click on the file from Windows and select Open with Excel. Very nice formatted output. Dianne Rhodes @ westat
|
Pages: 1 Prev: jackknife concept Next: SYMGET |