BookmarkSubscribeRSS Feed
cercig
Obsidian | Level 7

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

 

5 REPLIES 5
Reeza
Super User
Do you have access to SAS? If you push the data from SAS you may not have the same issues.
cercig
Obsidian | Level 7

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.

cercig
Obsidian | Level 7

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;

 

Reeza
Super User
You could try converting the date to a text field, but that seem similar to adding 21916 anyways.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 5 replies
  • 1424 views
  • 0 likes
  • 3 in conversation