Problem with EG referencing dynamic (macro-driven) PC files

[AndrewHowell]

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?

Many thanks.

[DF]

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.

Option Explicit
Dim app
Call dowork
'shut down the app
If not (app Is Nothing) Then
    app.Quit
    Set app = Nothing
End If
call FtpGet
Sub dowork()
    On Error Resume Next
    '----
    ' Start up Enterprise Guide using the project name
    '----
    Dim prjName
    Dim prjObject
    prjName = "\\home\projects\my report.egp"    'Project Name
    Set app = CreateObject("SASEGObjectModel.Application.4")
    If Checkerror("CreateObject") = True Then
        Exit Sub
    End If
    '-----
    ' open the project
    '-----
    Set prjObject = app.Open(prjName,"")
    If Checkerror("app.Open") = True Then
        Exit Sub
    End If
    '-----
    ' run the project
    '-----
    prjObject.run
    If Checkerror("Project.run") = True Then
        Exit Sub
    End If
    '-----
    ' Save the new project
    '-----
    prjObject.Save
    If Checkerror("Project.Save") = True Then
        Exit Sub
    End If
    '-----
    ' Close the project
    '-----
    prjObject.Close
    If Checkerror("Project.Close") = True Then
        Exit Sub
    End If
End Sub
Function Checkerror(fnName)
    Checkerror = False
    Dim strmsg
    Dim errNum
    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
    End If
End Function
Public Sub FtpGet()
    Dim remotepath, localpath, get1
    Dim vFTPServ
    Dim fNum
    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("bin")
    File1.WriteLine("get """ & get1 & """ """ & localpath & get1 & """")
    File1.WriteLine("close")
    File1.WriteLine("bye")
    File1.Close
    SET File1 = NOTHING
    SET FSO = NOTHING
    dim objShell
    set objShell = wscript.createObject("wscript.shell") 
    objShell.Run "cmd /c ftp -n -i -g -s:c:\ftpcommands_daily_report.txt" & " " & vFTPServ
    Set objShell = Nothing
End Sub
Function FormatDate(d)
    FormatDate = Year(d) & pd(Month(d), 2) & pd(Day(d), 2)
End Function
Function pd(n, totalDigits)
    If totalDigits > Len(n) Then
        pd = String(totalDigits - Len(n), "0") & n
    Else
        pd = n
    End If
End Function