ODS Markup ExcelXP
in [SAS]
|
From: Don Henderson on 8 Dec 2006 00:33 Comments from a birdie: When you get this type of pop up dialog, it often is an indication that you are using an out of date tagset. Jack Hamilton posted the location of the latest tagset (see his example code). Information on installing the tagset can be found on page 3 of this paper: http://support.sas.com/rnd/papers/index.html#excel2006 HTH, -don h On Thu, 7 Dec 2006 15:08:32 -0800, Jack Hamilton <jfh(a)STANFORDALUMNI.ORG> wrote: >This works for me: > >===== >//EMAILXLS JOB >//* >/*ROUTE PRINT FETCH >//* >//S1REPORT EXEC SASPROD, >// OPTIONS='noovp nocenter errorabend errorcheck=strict' >//XLSFILE DD DSN=&SYSUID..REPORT.XLS,DISP=(MOD,CATLG,DELETE), >// LRECL=8196,RECFM=VB,SPACE=(8196,(1000,2000)), >// UNIT=SYSWRK >//SYSIN DD *,DLM='!!' > >/* Get latest copy of tagset from SAS web site */ >filename tagset http > "%lowcase( > HTTP://SUPPORT.SAS.COM:80/RND/BASE/TOPICS/ODSMARKUP/EXCLTAGS.TPL > )"; >%include tagset / nosource2; >filename tagset clear; > >/* Empty the output file, in case this is a rerun. */ >data _null_; > file xlsfile old; >run; > >/* Tell SAS where to send output */ >ods tagsets.excelxp file=xlsfile record_separator=none > style=sansprinter; >ods listing close; > >/* Print the first sheet */ >ods tagsets.excelxp > options(sheet_name='Raw Data' > frozen_headers='1' > row_repeat='1' > autofilter='1-5' > ); > >title 'Raw Data'; > >proc print data=sashelp.prdsale noobs; > var country region division prodtype product year quarter month > actual predict; >run; > >/* Print the second sheet, triggered by a new ODS statement */ >/* to the ExcelXP tagset without a new FILE=. */ >ods tagsets.excelxp > options(sheet_name='Sales Summary' > autofilter='3' > ); > >proc report data=sashelp.prdsale missing nowindows nocenter; > column country region prodtype actual predict; > define country / group 'Country'; > define region / group 'Region'; > define prodtype / group 'Product Type'; > define actual / sum 'Actual Sales'; > define predict / sum 'Predicted Sales'; >run; > >ods _all_ close; >ods listing; > >/* Close and free the output file. */ >data _null_; > length dsn $44.; > file xlsfile mod close=free filename=dsn; > call symput('REPORTDSN', trim(dsn)); >run; > >filename email email > attach=("&REPORTDSN." > name='EmailXLS' > extension='xls' > lrecl=8196) > to='jfh(a)alumni.stanford.org' > from='Jack.Hamilton(a)kp.org' > subject='Excel test'; > >data _null_; > file email; > put 'Results are attached.'; >run; > >filename email clear; > >!! >===== > >record_separator=none is important on MVS. > > > >On Wed, 6 Dec 2006 11:59:27 -0500, "McWhorter, Keith" ><Kmcwhort(a)GTA.GA.GOV> said: >> Hi, >> >> >> >> I'm using an example from the paper "SAS Excels!" to create output as >> XLS to open in Excel. However, I'm running this on the mainframe and >> emailing it to myself. When I open the file it's all garbage. I've used >> the email part of the code with PDFs successfully. Not sure about all >> the parameters I may need for XLS though. >> >> Anyone see anything glaringly wrong? >> >> >> >> Thanks! >> >> >> >> Environment: z/OS 1.4, SAS 9.1.3 SP4 >> >> >> >> Code: >> >> >> >> ods listing close; >> >> ods markup tagset=excelxp body='SGSS.SAS.HTML(DHRT)' ; >> >> >> >> PROC PRINT data=dhrtb split='*' noobs; >> >> VAR job jesnr sdate stime exectm cputm edate etime; >> >> FORMAT sdate edate MMDDYY10. >> >> stime etime TIME8. >> >> ; >> >> Label sdate = 'Start*Date' >> >> stime = 'Start*Time' >> >> edate = 'End*Date' >> >> etime = 'End*Time' >> >> job = 'Job' >> >> ; >> >> run; >> >> ods markup close; >> >> >> >> filename tempmail email >> >> FROM=("SAS(a)gta.ga.gov") >> >> TO=("kmcwhort(a)gta.ga.gov") >> >> subject="DHR Jobs" >> >> type="text/plain" >> >> attach=("SGSS.SAS.HTML(DHRT)" >> >> content_type="application/xls" extension='xls'); >> >> >> >> DATA _NULL_; >> >> FILE tempmail; >> >> put 'As requested, attached is a listing of'; >> >> put '@DM Jobs Run on SYSTB November 13 - 30'; >> >> run; >> >> /* >> >> >> >> Keith McWhorter >> >> Software Systems Technical Lead >> >> Georgia Technology Authority >> >> 404-656-9068 >> >> >> >> Take the attitude of a student: never be too big to ask questions, never >> know too much to learn something new. >> >> --- Og Mandino --- >> >> >> >> >> >> >-- >Jack Hamilton >Sacramento, California >jfh(a)alumni.stanford.org
From: Jonas Bilenas on 8 Dec 2006 12:33 On Fri, 8 Dec 2006 00:33:36 -0500, Don Henderson <donaldjhenderson(a)HOTMAIL.COM> wrote: >Comments from a birdie: > > When you get this type of pop up dialog, > it often is an indication that you are > using an out of date tagset. > > Jack Hamilton posted the location of the > latest tagset (see his example code). > Information on installing the tagset can > be found on page 3 of this paper: > > http://support.sas.com/rnd/papers/index.html#excel2006 > >HTH, >-don h > >On Thu, 7 Dec 2006 15:08:32 -0800, Jack Hamilton <jfh(a)STANFORDALUMNI.ORG> >wrote: > >>This works for me: >> >>===== >>//EMAILXLS JOB >>//* >>/*ROUTE PRINT FETCH >>//* >>//S1REPORT EXEC SASPROD, >>// OPTIONS='noovp nocenter errorabend errorcheck=strict' >>//XLSFILE DD DSN=&SYSUID..REPORT.XLS,DISP=(MOD,CATLG,DELETE), >>// LRECL=8196,RECFM=VB,SPACE=(8196,(1000,2000)), >>// UNIT=SYSWRK >>//SYSIN DD *,DLM='!!' >> >>/* Get latest copy of tagset from SAS web site */ >>filename tagset http >> "%lowcase( >> HTTP://SUPPORT.SAS.COM:80/RND/BASE/TOPICS/ODSMARKUP/EXCLTAGS.TPL >> )"; >>%include tagset / nosource2; >>filename tagset clear; >> >>/* Empty the output file, in case this is a rerun. */ >>data _null_; >> file xlsfile old; >>run; >> >>/* Tell SAS where to send output */ >>ods tagsets.excelxp file=xlsfile record_separator=none >> style=sansprinter; >>ods listing close; >> >>/* Print the first sheet */ >>ods tagsets.excelxp >> options(sheet_name='Raw Data' >> frozen_headers='1' >> row_repeat='1' >> autofilter='1-5' >> ); >> >>title 'Raw Data'; >> >>proc print data=sashelp.prdsale noobs; >> var country region division prodtype product year quarter month >> actual predict; >>run; >> >>/* Print the second sheet, triggered by a new ODS statement */ >>/* to the ExcelXP tagset without a new FILE=. */ >>ods tagsets.excelxp >> options(sheet_name='Sales Summary' >> autofilter='3' >> ); >> >>proc report data=sashelp.prdsale missing nowindows nocenter; >> column country region prodtype actual predict; >> define country / group 'Country'; >> define region / group 'Region'; >> define prodtype / group 'Product Type'; >> define actual / sum 'Actual Sales'; >> define predict / sum 'Predicted Sales'; >>run; >> >>ods _all_ close; >>ods listing; >> >>/* Close and free the output file. */ >>data _null_; >> length dsn $44.; >> file xlsfile mod close=free filename=dsn; >> call symput('REPORTDSN', trim(dsn)); >>run; >> >>filename email email >> attach=("&REPORTDSN." >> name='EmailXLS' >> extension='xls' >> lrecl=8196) >> to='jfh(a)alumni.stanford.org' >> from='Jack.Hamilton(a)kp.org' >> subject='Excel test'; >> >>data _null_; >> file email; >> put 'Results are attached.'; >>run; >> >>filename email clear; >> >>!! >>===== >> >>record_separator=none is important on MVS. >> >> >> >>On Wed, 6 Dec 2006 11:59:27 -0500, "McWhorter, Keith" >><Kmcwhort(a)GTA.GA.GOV> said: >>> Hi, >>> >>> >>> >>> I'm using an example from the paper "SAS Excels!" to create output as >>> XLS to open in Excel. However, I'm running this on the mainframe and >>> emailing it to myself. When I open the file it's all garbage. I've used >>> the email part of the code with PDFs successfully. Not sure about all >>> the parameters I may need for XLS though. >>> >>> Anyone see anything glaringly wrong? >>> >>> >>> >>> Thanks! >>> >>> >>> >>> Environment: z/OS 1.4, SAS 9.1.3 SP4 >>> >>> >>> >>> Code: >>> >>> >>> >>> ods listing close; >>> >>> ods markup tagset=excelxp body='SGSS.SAS.HTML(DHRT)' ; >>> >>> >>> >>> PROC PRINT data=dhrtb split='*' noobs; >>> >>> VAR job jesnr sdate stime exectm cputm edate etime; >>> >>> FORMAT sdate edate MMDDYY10. >>> >>> stime etime TIME8. >>> >>> ; >>> >>> Label sdate = 'Start*Date' >>> >>> stime = 'Start*Time' >>> >>> edate = 'End*Date' >>> >>> etime = 'End*Time' >>> >>> job = 'Job' >>> >>> ; >>> >>> run; >>> >>> ods markup close; >>> >>> >>> >>> filename tempmail email >>> >>> FROM=("SAS(a)gta.ga.gov") >>> >>> TO=("kmcwhort(a)gta.ga.gov") >>> >>> subject="DHR Jobs" >>> >>> type="text/plain" >>> >>> attach=("SGSS.SAS.HTML(DHRT)" >>> >>> content_type="application/xls" extension='xls'); >>> >>> >>> >>> DATA _NULL_; >>> >>> FILE tempmail; >>> >>> put 'As requested, attached is a listing of'; >>> >>> put '@DM Jobs Run on SYSTB November 13 - 30'; >>> >>> run; >>> >>> /* >>> >>> >>> >>> Keith McWhorter >>> >>> Software Systems Technical Lead >>> >>> Georgia Technology Authority >>> >>> 404-656-9068 >>> >>> >>> >>> Take the attitude of a student: never be too big to ask questions, never >>> know too much to learn something new. >>> >>> --- Og Mandino --- >>> >>> >>> >>> >>> >>> >>-- >>Jack Hamilton >>Sacramento, California >>jfh(a)alumni.stanford.org Also make sure that you are using the correct version of Microsoft Excel (2002 or later).
From: "McWhorter, Keith" on 13 Dec 2006 14:55
Hey! I finally got my report in Excel from the mainframe. Thanks so much to everyone who replied. I was in a SAS class last week (for EBI) and I'm just now getting a chance to experiment with your suggestions. This ended up working: ods markup tagset=excelxp body='SGSS.SAS.HTML(DHRT)' record_separator=none style=sansprinter; Thanks again, Keith McWhorter |