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
精彩评论