I've come accross a behavior that seems odd to me when generating xml using ODS TAGSETS.ExcelXP. The short description is I can't open the xml in Excel. The long description is below, but first:
SAS environment
Software: SAS (r) Proprietary Software Release 9.3 TS1M2, submitted via SAS EG 7.12 HF4
Machine: AIX, oslevel = 7100-03-03-1415
Windows environment
Software: Excel under MS Office Professional Plus 2010. Excel Version 14.0.6129.5000 (32 bit).
Machine: Windows 7 Enterprise, Service Pack 1 (64 bit); Intel Core i5-4310M CPU, 2.70 GHz, 8 GB RAM
Now the the specifics of the behaviour. I'll post SAS code below.
I have an 84 position character string in my SAS dataset. I want to transform my SAS dataset into an Excel file. Using Proc Print and tagset ExcelXP, I create xml which I then try to open in Excel. It won't open. I've traced the problem to the following line in the xml:
<Cell ss:StyleID="data__l1" ss:Index="64"><Data ss:Type="Number">000000000000000000000000000000000000000000000000000000000000000000000000000000000000</Data></Cell>
Apparently what's happening is that Excel when it sees a number that is 84 digits long, tries to convert the number into scientific notation. It can't seem to do it when the character string is all zeroes. At least that's my surmise. Whether my surmise is correct or not, when I change "Number" to "String", Excel opens the xml file without a glitch.
<Cell ss:StyleID="data__l1" ss:Index="64"><Data ss:Type="String">000000000000000000000000000000000000000000000000000000000000000000000000000000000000</Data></Cell>
Now, given that the data is defined as $80. in SAS, I would have expected the xml to have consistently contained "String". However, sometimes the xml for the above cell is "String" in one row and sometimes it is "Number" in another row. Apparently, SAS assigns the type based on the contents of the cell not on the SAS definition, and apparently SAS assigns the type on a row by row basis. Different rows will have different data types in the same column based on the contents of the cell. I guess I would have expected the type to be set based on the SAS data type. Yes, I know format is generally not perserved, particularly for dates, but for Number vs. String, I would have expected that the xml Type be set based on the SAS data type not by the cell contents on a row by row basis.
Of course it doesn't matter what I expect, I not being the center of the SAS universe, 🙂 but the xml rendered can't be opened by Excel, which is a problem.
So, am I doing something wrong? Is there some option or parameter that I should be using. Yes, I'm sure I could control things more using Proc Report instead of Proc Print, but this is just a quickie throwaway program where I'm dumping things into Excel for presentation purposes for a daily defect meeting. And, yes, I'm aware that I can just switch to the html tagset which doesn't have this problem, and then do the detailed formatting in Excel but I have to ask: Is this a bug? Shouldn't the xml generated by ExcelXP always be readable by Excel?
Code snippet (Proc Format, Proc Template, ODS code, and Proc Print code):
PROC FORMAT;
VALUE Highlight_Num . - .E = 'WhiteSmoke'
-9999999999 - -1 = 'Pink'
0 - 9999999998 = 'Red'
9999999999 - HIGH = 'Black'
;
VALUE Highlight_Reg LOW - HIGH = 'WhiteSmoke'
;
VALUE $Highlight_Reg LOW - HIGH = 'WhiteSmoke'
;
VALUE $Highlight_Cntl '^' = 'Black'
'ZZZZZZZ' = 'Black'
OTHER = 'WhiteSmoke'
;
VALUE $Highlight_Char '^' = 'Black'
'ZZZZZZZ' = 'Black'
'X' = 'Yellow'
'XX' = 'Yellow'
'XXX' = 'Yellow'
'XXXXX' = 'Yellow'
'XXXXXX' = 'Yellow'
'XXXXXXX' = 'Yellow'
'XXXXXXXX' = 'Yellow'
'XXXXXXXXX' = 'Yellow'
'XXXXXXXXXX' = 'Yellow'
'XXXXXXXXXXX' = 'Yellow'
'XXXXXXXXXXXX' = 'Yellow'
'XXXXXXXXXXXXX' = 'Yellow'
'XXXXXXXXXXXXXX' = 'Yellow'
'XXXXXXXXXXXXXXX' = 'Yellow'
'XXXXXXXXXXXXXXXX' = 'Yellow'
'XXXXXXXXXXXXXXXXX' = 'Yellow'
'XXXXXXXXXXXXXXXXXX' = 'Yellow'
;
RUN;
PROC TEMPLATE;
DEFINE STYLE styles.XLsansPrinter;
PARENT = styles.sansPrinter;
STYLE HEADER FROM HEADER /
font_face = "Calibri"
font_size = 8pt
foreground = Black
background = DarkGray
just = CENTER
vjust = BOTTOM
;
END;
RUN;
ODS TAGSETS.ExcelXP options(frozen_headers='Yes' FontName="Calibri" Size="8") FILE="&CompBase" PATH="&XMLlib." STYLE=styles.XLsansPrinter;
PROC PRINT DATA=WORK.&Comp_Base.2 (OBS=&PrintObs1);
VAR _OBS_
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR _TYPE_
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_PIN_N
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_TIN
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_ACCOUNT_NUMBER
TIP_TRADE_BASE_DT_MDCY
TIP_STATE_CODE
TIP_ID
TIP_ENHANCED_SPECIAL_CMT
TIP_DATE_OPENED
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_DATE_OPENED_MONTH
TIP_DATE_OPENED_DAY
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_DATE_REPORTED
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_DATE_REPORTED_MONTH
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_AMT1_QUAL
TIP_AMT2_QUAL
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_AMOUNT_1
TIP_AMOUNT_2
TIP_INQUIRY_AMOUNT
TIP_PUB_REC_AMOUNT
TIP_BKRPT_LIABILITY_AMT
TIP_SUBSCRIBER_ID
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_ACCT_TYPE_CD
TIP_ENHANCED_ACCT_TYPE_CD
TIP_TERMS
TIP_TERMS_V08
TIP_TERMS_FREQ_CODE
TIP_STATUS_CODE
TIP_ENHANCED_STATUS_CODE
TIP_ACCOUNT_COND_CODE
TIP_LEGAL_DESIGNATOR_CODE
TIP_ECOA_CODE
TIP_TR_BALANCE_DATE
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_TR_BAL_DATE_MONTH
TIP_TR_BAL_DATE_DAY
TIP_BALANCE_AMOUNT
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_PR_STATUS_DATE
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_R_STATUS_DATE_MONTH
TIP_PR_STATUS_DATE_DAY
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_PR_FILE_DATE
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_PR_FILE_DATE_MONTH
TIP_PR_FILE_DATE_DAY
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_INQUIRY_DATE
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_INQUIRY_DATE_MONTH
TIP_INQUIRY_DATE_DAY
TIP_AMOUNT_PAST_DUE
TIP_SCH_PMT_AMOUNT
TIP_ACT_PMT_AMOUNT
TIP_BALLOON_PMT_AMOUNT
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_DEFER_FUTURE_PAY_DATE
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_PAST_PMT_DATE_MONTH
TIP_FUT_PMT_DATE_MONTH
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_LAST_PMT_DATE
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_LAST_PMT_DATE_MONTH
TIP_COMPANY_ID
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_KOB_CODE
TIP_ORIG_CREDITOR_CODE
TIP_SEC_AGENCY_CODE
TIP_CII_IND
TIP_CCC_CODE
TIP_PORTFOLIO_CODE
TIP_MORTGAGE_ID
TIP_GRID_V08
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_CREDIT_LIMIT
TIP_HIGH_BALANCE
TIP_CHARGEOFF_AMOUNT
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_DELINQ_DATE_1
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_DELINQ_DATE_1_MONTH
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_DELINQ_DATE_2
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_DELINQ_DATE_2_MONTH
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_MAX_DELINQ_DATE
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_MAX_DELINQ_DATE_MONTH
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_MAX_DELINQ_CODE
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_30_DAY_DELINQ_CT
TIP_60_DAY_DELINQ_CT
TIP_90_DAY_DELINQ_CT
TIP_DEROG_CT
TIP_ORIGINAL_LOAN_AMT
/ STYLE(DATA) = [BACKGROUND=Highlight_Reg. font=("Calibri",8pt) ]
;
VAR TIP_SPECIAL_PAYMENT_CODE
TIP_TRADE_INCLUSION_FLAG
/ STYLE(DATA) = [BACKGROUND=$Highlight_Reg. font=("Calibri",8pt) ]
;
RUN;
ODS TAGSETS.ExcelXP CLOSE;
... View more
