FTP

5月 202013
 

Last week I alluded to some very useful applications of the Copy Files task. This is one of them.

Using the SAS programming language, you can manipulate data and create files of just about any size, shape, and format: Excel, PDF, CSV, RTF, and more. A challenge for SAS Enterprise Guide users has been: how to capture those files and bring them back to your local PC, when the SAS Workspace is running on a remote machine?

Example: Export to a CSV file and download the result

Here's a typical scenario: You have a simple SAS program that produces one or more CSV files that you will ultimately use in another program. How can you get the CSV files to your PC automatically?

STEP 1: Build a program step to create the CSV file
This program is easy to adapt for any data set and environment. It works on Windows and UNIX. All you need to know is the library and member name of the data that you want to export, and then the destination folder for your local PC. The program will perform the export operation, stage the CSV file in a temp location, and define the macro variables that the next step will use.

/* Data to export */
%let lib  =         sashelp;
%let datafile =     class;
 
/* Local folder to download to */ 
%let download_to =  c:\projects\data\results;
 
/* detect proper delim for UNIX vs. Windows */
%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));
 
%let download_from =
  %sysfunc(getoption(work))&delim.&datafile..csv;
 
filename src "&download_from.";
 
proc export data=&lib..&datafile.
  dbms=csv 
  file=src
  replace;
run;
 
filename src clear;

STEP 2: Use Copy Files task to download the result
The Copy Files task accepts SAS macro expressions. That's a key feature, as the macro variables we need are defined in the previous program step. Here's a screen shot of the task settings:

This makes the use of the Copy Files task very "generic". In fact, you can create a Task Template that defines these exact task settings, and thus always have it available on your Tasks menu directly.

STEP 3: Link these steps together in a process flow
Create a user-defined link between the program and the task, ensuring that they will run in the correct sequence.

THAT'S IT!
The power of SAS and the flexibility of the Copy Files task really makes this a simple operation. However, you might want to consider a few variations:

  • Export and download a collection of files in one step. With minor mods to the SAS program, you can loop through a collection of SAS data sets and export multiple CSV files. Instead of defining a single file to download, set the &DOWNLOAD_FROM variable to a file spec with a wildcard. The Copy Files task can handle wildcard notation -- no problem. (Well, no problem anymore, as long as you grab this update.)

    /* specify a wildcard */
    %let download_from =
      %sysfunc(getoption(work))&delim.%str(*).csv;
     
    /* file to create in step */
    filename src "%sysfunc(getoption(work))&delim.&datafile..csv";
  • Add a date stamp to your results file. You might have a requirement to keep older versions of your results. With a simple adjustment to the macro expression, you can append a date stamp to the files you create. This will ensure that even if you download the results to the same location each day, the previous results will not be replaced. When you download the file, the name with the date stamp will be intact.
    filename src 
     "%sysfunc(getoption(work))&delim.&datafile._%trim(%sysfunc(today(),date9.)).csv";

    Sample result from this step:

    NOTE: The file SRC is:
          Filename=/sas/work/class_19MAY2013.csv,
    

This is just one example of the useful things you can do with the Copy Files task. SAS users are a creative bunch. What other uses can you think of for this task?

Related articles

Copying files in SAS Enterprise Guide
Fixes for the Copy Files task in SAS Enterprise Guide

tags: FTP, SAS custom tasks
5月 172013
 

A few months ago I released the Copy Files task for use with SAS Enterprise Guide. The task allows you to transfer any files between your PC and a SAS Workspace session, much like an FTP process. It doesn't rely on FTP though; it uses a combination of SAS code, Windows APIs, and SAS Integration Technologies to get the job done.

It's proven to be a very popular task, because it can be useful in so many situations. It even earned a mention in a SAS Global Forum paper this year (and no, it wasn't a paper that I wrote).

Today I'm going to point out the things that the task doesn't do so well. Or at least, that it didn't do well until I made some updates. My changes were based on two "complaints" from several SAS users.

Read on for the details. But if you don't care and you just want the latest version of the task, you can download it from here.

Complaint #1: Wildcards that are a little too "wild"

The task allows you to use wildcard characters in your file specifications so that you can match multiple files to transfer. A problem occurs though, when your file specification looks like this:

/usr/local/data/*.xls

Can you guess the problem? What if I told you that the task stores your file specification in a SAS macro variable? Yep, it's that "/*" sequence in the value that trips things up, because SAS interprets it as the start of a comment. Left unchecked, this sabotages the remainder of the SAS code that is included in the process.

The SAS macro experts are already shouting out the answer to fix this: use %STR to wrap the slash and "hide" the token from the SAS parser. That's a great idea! Except that the task relies on the SAS "internal" value for this value --and not the displayed value -- when it comes time to process. These values are different when %STR wraps a special character like the forward slash. The macro facility changes out this character with a hexadecimal character called a delta character.

To illustrate, I used another popular custom task -- the SAS Macro Variable Viewer -- to show the inner value of a SAS macro variable:

Notice the funky arrow characters. Is that what you were expecting?

Now the task detects the presence of a forward slash (and some other special characters) and will automatically add the %STR so you don't have to. (But you can still use %STR if you want to.) And it correctly detects the delta characters, if present, to convert them back to their correct form before trying to use the value.

Complaint #2: Fixing line-ending characters but breaking other stuff

Users of FTP might be familiar with binary versus ASCII mode for file transfers. Because UNIX line-endings are different than Windows line-endings for text files, transferring a file in ASCII mode helps to ensure proper line-ending behavior for the target host.

The Copy Files task transfers ALL files using a binary mode. Why? Because in today's global workplace even text-based files often don't adhere to the limited English-centric ASCII standard. Attempting a text-based file transfer could result in encoding mismatches, so it's much safer to transfer content as "binary blobs".

But you still want your text files to have the proper line endings for the target host. To answer that, the Copy Files task offers a "Fix line-ending characters" option that does the following:

  • Scans the file to determine whether it's a text file. (This relies on the file content and not on special file extensions such as .TXT or .CSV.).
  • Rewrites the file and replaces the line-ending characters as needed for the target file system (Windows or UNIX).

The problem was that in rewriting the file (using Windows-based StreamReader and StreamWriter functions), the Copy Files task was changing the file encoding to UTF-8. That encoding works fine on Windows and most users didn't even notice. But some users sent me output from file dump tools and comparisons that showed the byte-order mark characters that were added to the file. (SAS users: I knew I could count on you!)

To address this, I changed the "fix line endings" process to use lower level I/O functions that simply scan through the text files as a binary stream, byte-for-byte, and change the line endings as needed. Trying to decide on proper encoding is risky business, so I decided to leave the character encoding untouched.

In addition to my own testing, a couple of users out there have confirmed that my changes fix the issues -- at least for now. Thanks for that! If you want to try the latest, get it now from here:

>> Download the Copy Files task

Related articles

Copying files in SAS Enterprise Guide
Inspecting SAS macro variables in SAS Enterprise Guide

tags: FTP, macro programming, SAS custom tasks, SAS Enterprise Guide
12月 072012
 

One of the problems that trips up experienced SAS users when they begin to use SAS Enterprise Guide is a result of simple geography.

The SAS Enterprise Guide application runs here, on your desktop. The SAS Workspace session (which accesses data and cranks through your analysis) runs over there, on a remote machine. If you're accustomed to "PC SAS" running all on your local box, you might be forced to rethink a few of your processes. For example, do you have Excel files on your PC to import using PROC IMPORT? Does your SAS program create output that you have to save on your PC? How will you get these files to where they need to be?

There are some strategies that help, including mapped drives, UNC paths, and network shares via a mechanism like Samba. These mechanisms allow your local SAS Enterprise Guide and your remote SAS to "see" a shared location on the network. Also, SAS Enterprise Guide has a few focused tasks that can "move" files for you under the guise of "import" and "export" steps.

But there isn't a general method to copy any file you want from your PC to the SAS Workspace, or from the SAS Workspace to your PC. Until now.

I give you: the Copy Files task for SAS Enterprise Guide.

What "Copy Files" does

The Copy Files task allows you to capture file transfer steps within your SAS Enterprise Guide process flow. You can upload files from your local PC to the remote machine where your SAS Workspace is running. And you can download files from your remote SAS Workspace to a folder on your local PC. It is similar in concept to an FTP process, except that this transfer operation uses your already-existing connection to a SAS Workspace and does not require a separate application.

Some readers might be familiar with SAS/CONNECT Data Transfer Services (PROC UPLOAD and PROC DOWNLOAD), which allows you to transfer files between two SAS sessions. Again, this is similar in concept, but since there is only one SAS session in play here, we need to use a different mechanism.

How to copy files in bulk

The Copy Files task supports two features that allow you to copy multiple files with a single step. First, you can use wildcard characters to match on multiple file names. An asterisk (*) matches all characters, in any number, before the next non-wildcard character. A question mark (?) matches any single character that occupies that position in the file name.

Second, you can use SAS macro variables and expressions to specify any part of the source file names or destination folder. Use a SAS program to determine the files you need to copy earlier in the process, assign that value to a macro variable, and then have the task "dynamically" select the correct files when it's time to copy them.

Support for Task Templates

The Copy Files task also supports a standard SAS Enterprise Guide feature: Task Templates. You can create an instance of the task that is useful in one process, then save those task settings to your personalized set of Task Templates that you can use in another process or even in another project. To save your settings as a template, right-click on the Copy Files task within your process flow and select Create Task Template. To use the new template, select it as a menu item from Tasks->Task Templates.

Limitations to note

This task does not generate a SAS program that you can reuse in a batch SAS process or SAS stored process. The task works by using SAS Workspace APIs to transfer data across your network connection, so all of the work happens "behind the scenes". However, the task does create a detailed log output that shows what files were copied, how many bytes were transferred, how long it took, and whether there were any errors.

How to get the task

You can download the Copy Files task from this link (Zip file). The download package includes three versions that depend on your version of SAS Enterprise Guide: one for v4.3, one for v5.1, and one for v4.1 (with a reduced feature set). There is also a documentation file (PDF) that provides instruction for how to install and use the task.

tags: Copy Files, FTP, PROC DOWNLOAD, PROC UPLOAD, SAS custom tasks, SAS Enterprise Guide