SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Reply
Contributor
Posts: 29

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

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

 

Super User
Posts: 18,997

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

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

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

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.

Contributor
Posts: 29

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

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;

 

Super User
Posts: 18,997

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

You could try converting the date to a text field, but that seem similar to adding 21916 anyways.
Super User
Super User
Posts: 7,671

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

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).

Ask a Question
Discussion stats
  • 5 replies
  • 529 views
  • 0 likes
  • 3 in conversation