topic Re: Excel-VBA transfers incorrect date values from SAS to Excel-sheet in Microsoft Integration with SAS

Excel-VBA transfers incorrect date values from SAS to Excel-sheet

cercig — Fri, 06 Nov 2015 15:58:33 GMT

My VBA code transfers a SAS-table to Excel-sheet via ADODB.Connection and "SAS.LocalProvider.1". Two SAS-columns are date in the format of YYYY-MM-DD, like 2015-09-01 and 2015-09-30.

However, in the end, I get date values in the Excel-sheet like 1955-08-31 and 1955-09-29 instead of 2015-09-01 and 2015-09-30.

I tried a code like below, but it didn't work. It still transfers the date as "1955-08-31". I found a SAS-documentation about this issue, but it only recommends to add 21916 to the SAS-date to get the Excel-date, SAS-Doc. Is it really the only solution?

Public Sub SASTransfer()
  Dim rTarget1 As Range: Set rTarget1 = Sheet2.Range("A2")
  Dim sSasTable1 As String: sSasTable1 = SASOutputPath & "\" & SASOutput1 & ".sas7bdat"

    Dim con1 As New ADODB.Connection ' This creates a new connection
    Dim rs1 As New ADODB.Recordset   ' This creates a new RecordSet
    con1.Provider = "SAS.LocalProvider.1"    'Using the SAS.LocalProvider to transfer the SAS-table into Excel 
    con1.Open                        ' This opens the connection, con1
    rs1.ActiveConnection = con1      ' This describes the recordset, rs1, as the active connection set
    
    rs1.Properties("SAS Formats") = "PeriodFrom=YYMMDD10." 'This presents the format of the SAS-column "PeriodFrom"
                                                           ' I wrote the date format, YYMMDD10., as shown inside the SAS

    rs1.Open sSasTable1, con1, adOpenForwardOnly, adLockReadOnly, ADODB.adCmdTableDirect 
    ' The line above basically describes the SAS-table "sSasTable1" and other transfer properties
    rTarget1.CopyFromRecordset rs1     ' This line copies the SAS-table, rs1, to the target range inside the Excel-sheet
    rs1.Close
    Set rs1 = Nothing
    con1.Close
    Set con1 = Nothing

End Sub

Re: Excel-VBA transfers incorrect date values from SAS to Excel-sheet

Reeza — Fri, 06 Nov 2015 16:08:55 GMT

Do you have access to SAS? If you push the data from SAS you may not have the same issues.

Re: Excel-VBA transfers incorrect date values from SAS to Excel-sheet

cercig — Fri, 06 Nov 2015 16:14:24 GMT

I guess you are right, but I try to do the whole job inside the VBA, like running the SAS-code inside VBA and then tranferring the table inside VBA. One run will be enough to fix everything, it was the aim.

Re: Excel-VBA transfers incorrect date values from SAS to Excel-sheet

cercig — Fri, 06 Nov 2015 16:28:35 GMT

I tried many methods, but only the method below worked. Adding 21916 to the date inside my SAS-code. It is not perfect, it creates the SAS-table with the date as "2079-09-30", but at least it works for my "one-run" purpose.

data excel_export;
     set have;
     PeriodFrom = PeriodFrom + 21916;
     format PeriodFrom YYMMDD10.;
run;

Re: Excel-VBA transfers incorrect date values from SAS to Excel-sheet

RW9 — Fri, 06 Nov 2015 16:50:27 GMT

I would imagine so, never seen someone try to extract data from a dataset with VBA. SAS stores dates as number from a certain date, Excel's dates are from another date. Hence that need to add a certain value which is the difference between those two base dates. Out of interest, why are you trying it this way? Why not just export the data directly from SAS, you can set formats then at that level.

Alternatively, export the data from SAS as CSV, then in your Excel file, the VBA can directly load the CSV data, and send to the Worksheet, you wouldn't have this date conversion then. I would recommend CSV as transfer format between SAS and Excel (as Excel is not a database, so doesn't work the way you think it will).

Re: Excel-VBA transfers incorrect date values from SAS to Excel-sheet

Reeza — Fri, 06 Nov 2015 16:53:15 GMT

You could try converting the date to a text field, but that seem similar to adding 21916 anyways.