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
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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!