11-06-2015 10:58 AM
My VBA code transfers a SAS-table to Excel-sheet via
"SAS.LocalProvider.1". Two SAS-columns are date in the format of
However, in the end, I get date values in the Excel-sheet like
1955-09-29 instead of
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
11-06-2015 11:14 AM
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.
11-06-2015 11:28 AM
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;
11-06-2015 11:50 AM
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).