topic Re: ADO connection from Excel - SAS date format problem - rs.Properties("SAS Formats") = "_ALL_" NOT WORKING in Microsoft Integration with SAS

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

tom12122 — Tue, 13 Nov 2012 13:27:40 GMT

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

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

KenjiAneel — Tue, 04 Feb 2014 20:08:16 GMT

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.

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

KenjiAneel — Tue, 04 Feb 2014 20:11:47 GMT

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

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

BU2B — Wed, 11 Mar 2015 19:05:55 GMT

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