ADO connection from Excel - SAS date format problem - rs.Properties("SAS Formats") = "_ALL_" NOT WORKING

[tom12122]

I try to connect Excel to SAS using ADO but encountered a problem  - SAS returns recordset that contain DATE columns only in SAS format eg. 19236 (instead 2012-08-31)

I went through whole http://www.technion.ac.il/docs/sas9/book_files/58657.pdf and tried to set :

rs.Properties("SAS Formats") = "_ALL_"

rs.Properties("SAS Informats") = "_ALL_"

which should enforce SAS to use data type defined in table (all date formats and informats are YYMMDD10.) .

Itried also

rs.Properties("SAS Formats") = "date=YYMMDD10."

rs.Properties("SAS Informats") = "date=YYMMDD10." 

NO SUCCESS!!!

My code is following:

Sub get_ado_table_from_sas()

On Error GoTo err:

Dim db As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim errorstring As String

Path = "C:\dokumenty\!sas_biblioteki\TESTOWE_OBLICZENIA\PR_NARZEDZIE"

Set obWS = obWSM.Workspaces.CreateWorkspaceByServer("Local", _

VisibilityProcess, Nothing, "", "", errorstring)

db.Open "provider=sas.iomprovider.1; SAS Workspace ID=" + obWS.UniqueIdentifier

rs.ActiveConnection = db

rs.Properties("SAS Formats") = "_ALL_"

rs.Properties("SAS Informats") = "_ALL_"

rs.Open "pr_arch.cal_repl_port_str", db, adOpenDynamic, adLockReadOnly, adCmdTableDirect

Cells(6, 1).CopyFromRecordset rs

rs.Close

db.Close

Exit Sub

err:

MsgBox err.Description

End Sub

[KenjiAneel]

I presume when you tried "_ALL_" you got your dates but in string format.

To identify the format of the specific Field you can use the OpenSchema method. In my case, I wanted to return vbDate if the field is identified as such:

Private Function DataSourceType(TableName As String, FieldName As String) As Integer

    Dim RSSchema As New ADODB.Recordset

    Dim i As Integer

    DataSourceType= 0

    Set RSSchema = pSourceDB.ActiveConnection.OpenSchema(adSchemaColumns, Array(Empty, Empty, TableName))

    Do Until RSSchema.EOF

        If RSSchema!COLUMN_NAME = FieldName Then

            If RSSchema!FORMAT_NAME = "DATE" Then DataSourceType= 7 ' adDate

            Exit Do

        End If

        RSSchema.MoveNext

    Loop

End Function

With this, I think you are all set.

[KenjiAneel]

In case you don't know, to convert SAS Date to Office Date you should add 21916 to the SAS date field:

if DataSourceType(Table, Field)=vbDate then rs.Field("date")=rs.Field("date")+21916

[BU2B]

Hi Kenji,

Could you expand a little on your last reply?  I can't get the Properties("SAS Informats") = "_ALL_" to work either.  What is the DataSourceType etc?

Thanks!

Steve