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