01-23-2012 12:24 AM
Happy New Year, all!
Hit an issue in a migration project. Grateful for feedback/suggestions:
- Old environment: PC/SAS 9.2, and SAS 9.2 on a Unix platform
- New environment: EG 4.3 locally, SAS 9.3 on Unix platform. (i.e, No local SAS.)
Need to migrate existing SAS programs, all previously run using the PC/SAS interface. Most consist just of RSUBMIT code to the server.
However, there is some locally processed code; mainly to import/export local files then PROC UPLOAD/DOWNLOAD to/from the server.
It was my intention to use EG's (non-SAS) functionality to import/export PC files to/from the SAS server.
Problem: In the existing code, some of the local file references contain dynamic (macro) path/file names, for example: "E:\Data\&Year\M&month\data.txt". Works fine with PC/SAS, but (server-based) macros mean little to (local) Enterprise Guide.
Question: Can EG accommodate this? I'm not hopeful, so also grateful for any suggestions re workarounds, for example, using a VBScript rename before/after?
01-23-2012 09:48 AM
I don't think there's a simple solution for this - it's a problem we've had since migrating to EG too. We use 4.1, so if there's a better way of doing this in the later versions, someone please let me know and I can add it to my list of reasons to upgrade(!).
Your local PC doesn't do any of the processing, as there is no SAS server running there any more. Everything has to be done by the remote server, which doesn't have access to files on your local PC. Enterprise Guide has its own built-in import/export routines, but these cannot be named dynamically based on your SAS code.
The solution we tend to use, is to have SAS output files to a folder somewhere on the SAS server itself. This allows you to use the same code as before (obviously converting local windows paths to "local" unix paths etc.), and then use something like FTP to retrieve the files.
Your server admin would have to create this location for you, and assign credentials for FTP.
Files can be retrieved manually using FTP, or automatically alongside scheduling your report (for example). Most of our processes are scheduled using code based on the Schedule option in Enterprise Guide's interface.
The below code is from EG 4.1, so you'll need to double check for any changes for 4.3. (There may be a simpler way of writing this, but its in VBScript so it was never going to be pretty!)
It will copy a file called "daily_output_20120123.csv" from /var/opt/users/DF/ to c:\outputs\. A file containing the relevant FTP commands is created localled - I normally rename this with each script to match the report name (to avoid overlaps).
The FormatDate() function formats today's date in the way that I need it - you may require customization.
All this code goes in the VBS file that EG creates for you automatically. Obviously the path to the EGP file would need updated as required.
'shut down the app
If not (app Is Nothing) Then
Set app = Nothing
On Error Resume Next
' Start up Enterprise Guide using the project name
prjName = "\\home\projects\my report.egp" 'Project Name
Set app = CreateObject("SASEGObjectModel.Application.4")
If Checkerror("CreateObject") = True Then
' open the project
Set prjObject = app.Open(prjName,"")
If Checkerror("app.Open") = True Then
' run the project
If Checkerror("Project.run") = True Then
' Save the new project
If Checkerror("Project.Save") = True Then
' Close the project
If Checkerror("Project.Close") = True Then
Checkerror = False
If Err.Number <> 0 Then
strmsg = "Error #" & Hex(Err.Number) & vbCrLf & "In Function " & fnName & vbCrLf & Err.Description
'MsgBox strmsg 'Uncomment this line if you want to be notified via MessageBox of Errors in the script.
Checkerror = True
Public Sub FtpGet()
Dim remotepath, localpath, get1
remotepath = "/var/opt/users/DF/"
localpath = "C:\Outputs\"
localpath = Trim(localpath)
If Right(localpath, 1) <> "\" Then localpath = localpath & "\"
remotepath = Trim(remotepath)
If Right(remotepath, 1) <> "/" Then remotepath = remotepath & "/"
get1 = "daily_output_" & Trim(FormatDate(Date-1)) & ".csv"
vFTPServ = "192.168.79.221"
Dim FSO, File1
Set FSO = CreateObject("Scripting.FileSystemObject")Set File1 = FSO.CreateTextFile("c:\ftpcommands_daily_report.txt", True)
File1.WriteLine("user username password")
File1.WriteLine("cd " & remotepath)
File1.WriteLine("get """ & get1 & """ """ & localpath & get1 & """")
SET File1 = NOTHING
SET FSO = NOTHING
set objShell = wscript.createObject("wscript.shell")
objShell.Run "cmd /c ftp -n -i -g -s:c:\ftpcommands_daily_report.txt" & " " & vFTPServ
Set objShell = Nothing
FormatDate = Year(d) & pd(Month(d), 2) & pd(Day(d), 2)
Function pd(n, totalDigits)
If totalDigits > Len(n) Then
pd = String(totalDigits - Len(n), "0") & n
pd = n