SAS Excelxp tagset

Sivanesh · New User Joined: 12 May 2011 Posts: 10 Location: India

Hi,

I need to attach the workbook in a email with multiple worksheets.I`m using SAS 9.1,i have copied the source code of TAGSETS.EXCELXP from SAS website into a datset and excuted using %include as you see in the code.i`m getting errors while executing the tagset wherever i have "^" and "[]" symbols.

I have attached the tagset

Could someone give me the correct code of Excelxp tagset or if there is any otherwasy to attach workbook in email.

//E0384T5T JOB ('SPRINT'),MSGCLASS=O,NOTIFY=E0384AC,TIME=(,1)
/*ROUTE XEQ NYMVS1
/*JOBPARM S=N090
//STEP1 EXEC K9337SA9,CONFIG=E0384AC.SIVANESH.SAS(EMAILTST)
//O1PDSE DD DSN=E0384T.STS.SJR.NESUG001.W05209(WSIVA),DISP=SHR
//O2PDSE DD DSN=E0384T.STS.SJR.NESUG001.W05209(SIVA),DISP=SHR
//O3PDSE DD DSN=E0384T.STS.SJR.NESUG001.WYYWYY,DISP=SHR
//EXCELTAG DD DSN=E0384T.SIVANESH.TAGSET,DISP=SHR
//SYSIN DD *
DATA WKLYDATA;
INFILE O1PDSE;
INPUT IDNUMBER $ 1-3 NAME $ 5-8;
RUN;
DATA DLYDATA;
INFILE O2PDSE;
INPUT IDNUMBER $ 1-3 NAME $ 5-8 SYM $ 10-13;
RUN;
%INCLUDE EXCELTAG / NOSOURCE2;
ODS TAGSETS.EXCELXP BODY='WSIVA'
PATH =O3PDSE(URL=NONE)
RECORD_SEPERATOR=NONE
PAGE='PAGE1';
RUN;
PROC PRINT DATA=WKLYDATA NOOBS;
RUN;
ODS TAGSETS.EXCELXP CLOSE;
ODS TAGSETS.EXCELXP BODY='WSIVA'
PATH =O3PDSE(URL=NONE)
RECORD_SEPERATOR=NONE
PAGE='PAGE2';
PROC PRINT DATA=DLYDATA NOOBS;
RUN;
....

Error:
break /if $options;
_
_
_
22
22
22
_
_
_
200
200
200

Robert Sample · Posted: Thu May 12, 2011 6:02 am

I suspect you're having code page problems.

Also, EBCDIC, sadly, does not have a single character pair to represent square brackets [] -- they can be X'BA' and X'BB', or they can be X'AD' and X'BD'.

Similarly, the symbol for the logical not is NOT a circumflex (or caret) as you indicated in your post -- the logical not symbol may be X'5F' or X'B0' depending upon the code page.

Your best bet is to edit your code in hex mode and ensure that these characters are set appropriately -- and if they don't work with one pair of hex values, try the other pair.

Sivanesh · New User Joined: 12 May 2011 Posts: 10 Location: India

Thanks Robert,

The hex value for ^ is X'B0' to X'5F' as '¬' and square brackets [] have been changed from X'BA' and X'BB', to X'AD' and X'BD' respectively.

There were no errors however the workbook has only one sheet with XML codes .

One more issue was when i resubmit the same job it abened with SEC6,I`m not sure of the reason for this ,when i changed the BODY name in SAS code the job ran fine.What could be the reason for this.

Please find the JCL attached and advice me whats wrong with that.

PeterHolland · Posted: Thu May 12, 2011 2:46 pm

support.sas.com/dsearch?Find=Search&ct=&qt=System+abend+SEC6+&col=suppprd&nh=10&qp=&qc=suppsas&ws=1&qm=1&st=1&lk=1&rf=0&oq=&rq=0

PeterHolland · Posted: Thu May 12, 2011 2:55 pm

PeterHolland · Posted: Thu May 12, 2011 2:58 pm

And FWIW :

www2.sas.com/proceedings/sugi31/115-31.pdf

superk · Posted: Thu May 12, 2011 10:24 pm

So, it would seem that SAS has come up with the answer for all of those topics we see regarding creating multiple Excel worksheets within a proper .xls formatted file.

<update>

I see now. They're generating XML code that the user can import into Excel. Something that's already been talked about in the past. So, as always, once you know how to create the proper XML code, you can use pretty much any tool or language to generate the data.

Sivanesh · New User Joined: 12 May 2011 Posts: 10 Location: India

Thanks to Robert and Peter,

I`m able to generate the workbook now with my SAS Code. I dowloaded the SAS tagset 9.1(ZIP) whih had correct version of ExcelXP tagset code.Earlier i used the ExcelXP tagset from same SAS website but it was incorrect.They have given many versions iof this tagset,need to pick the correct one.This time i haven't change my [] and ^ in the tagsets.