Desktop productivity for business analysts and programmers

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

Reply
Moderator
Posts: 246

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

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.

Frequent Contributor
Frequent Contributor
Posts: 94

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

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

Ask a Question
Discussion stats
  • 1 reply
  • 491 views
  • 0 likes
  • 2 in conversation