topic Re: Pivot Table VBA in SAS in Statistical Procedures

Pivot Table VBA in SAS

deleted_user — Thu, 04 Nov 2010 15:12:38 GMT

I have used the code in the link below to create a pivot table, which worked great. I was hoping to adapt the code to also create a calculated field from SAS code.

http://www2.sas.com/proceedings/sugi31/035-31.pdf

1. I would like to understand what these numbers refer to
-4157, -4112, and -4106

2.Create Calculated Field per "On the pivot table toolbar, choose PivotTable > Formulas > Calculated Field"

Re: Pivot Table VBA in SAS

Cynthia_sas — Sun, 07 Nov 2010 04:09:46 GMT

Hi:
The author of that paper provided his email address at the end of the paper (on page 4). You might want to contact him and ask what those numbers were used for.

cynthia

Also, for future reference, if you use < or > in your postings, it can sometimes cause your posting to appear truncated because the forum posting software uses < and > as HTML tags. This post
http://support.sas.com/forums/thread.jspa?messageID=27609毙
describes how you have to post < or > symbols in order to convey the correct character and avoid truncation.

Re: Pivot Table VBA in SAS

MattF — Tue, 09 Nov 2010 15:52:32 GMT

Those numbers of several of thousands Excel "magic" contstants:

From Excel, (alt-F11) Macro Editor, (F2) Object Browser,

Const xlTextQualifierNone = -4142 (&HFFFFEFD2)
Const xlSum = -4157 (&HFFFFEFC3)
Const xlCount = -4112 (&HFFFFEFF0)
Const xlAverage = -4106 (&HFFFFEFF6)

In this example they are passed as options to VB functions, e.g.

XL.Workbooks.OpenText
Sub OpenText(Filename As String, [Origin], [StartRow], [DataType], [TextQualifier

As XlTextQualifier = xlTextQualifierDoubleQuote], [ConsecutiveDelimiter], [Tab],

[Semicolon], [Comma], [Space], [Other], [OtherChar], [FieldInfo], [TextVisualLayout], [DecimalSeparator], [ThousandsSeparator], [TrailingMinusNumbers], [Local])

Best,
-Matt