Append data from SAS into Excel using SAS Office Add in

I have a SAS project that will want to be run monthly. The data generated needs to be loaded into an Excel workbook. Is 开发者_开发问答there a way to append the new months data onto the end of the previous months, without having to refresh all of the data?

---

You can be specific about where you paste the data, so perhaps if you track the location of where you want the next update to go, you could use something like the following to paste it there:

data _null_;
  set try;
  file dde "EXCEL|sheet!R10C1:R150C20" notab lrecl=2000; #sheet & cell refs;
  put var1 var2 varn;
run;

I've not use this before so I can't comment any further.

The method I'd recommend however, is to add the monthly update to a rolling historical dataset in SAS and then export all data to Excel. You have far greater control over formatting and any analysis you may wish to do in the future, inside SAS rather than in Excel.

---

Sure - using VBA & the IOM! This should do the trick:

Dim obSAS As SAS.Workspace
Dim obWorkspaceManager As New SASWorkspaceManager.WorkspaceManager
Dim obConnection As New ADODB.Connection

Sub Connect_to_SAS()
   Dim obServerDef As New SASWorkspaceManager.ServerDef
   Dim xmlString As String
   Dim errorXML As String
   Dim myUserid As String
   Dim myPwrd As String
   Dim myPort As String
   Dim myServer As String

  ' Enter these params
   myPort = 8561
   myServer = "blah.companyname.com"
   myUserid = "you@saspw"
   mytargetsheet = "Sheet1" ' where the data is going
   mytargetrow = 2 ' where the data gets pasted

   ' connect to sas
   obServerDef.Port = myPort
   obServerDef.Protocol = ProtocolBridge
   obServerDef.MachineDNSName = myServer
   myPwrd = InputBox("User = " & myUserid & vbCrLf & vbCrLf & _
    "Please enter SAS password below", "Login Prompt", "Password")
   If myPwrd = "" Then End
   Set obSAS = obWorkspaceManager.Workspaces.CreateWorkspaceByServer( _
        "My Ref", VisibilityProcess, obServerDef, myUserid, myPwrd, xmlString)
   If (Len(errorXML) > 0) Then MsgBox errorXML
    'submit your sas code
   obSAS.LanguageService.Submit "data x; x=1; run;"
   ' retrieve data (cols not needed as we are doing an append)
    Dim obRecordSet As New ADODB.Recordset
    obConnection.Open "provider=sas.iomprovider.1; SAS Workspace ID=" _
        + obSAS.UniqueIdentifier
    obRecordSet.Open "work.x", obConnection, adOpenStatic, adLockReadOnly _
        , adCmdTableDirect
    Sheets(mytargetsheet).Cells(mytargetrow, 1).CopyFromRecordset obRecordSet
   ' close session
    obWorkspaceManager.Workspaces.RemoveWorkspace obSAS
    obSAS.Close
End Sub