Export and download any file from SAS Enterprise Guide

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

7 Comments

  1. Posted June 10, 2013 at 3:30 am | Permalink

    You have really posted too good info for Export and download any file from SAS Enterprise Guide. It became too simple and easy for me with diagrams to understand...

  2. Posted July 18, 2013 at 3:29 am | Permalink

    This is a very useful contribution. Thanks a lot for sharing this!

  3. Posted September 25, 2013 at 4:32 am | Permalink

    Hy Chris,

    thanks a lot for sharing this nice Add-In with us!

    Do you plan to develop a Version for the 6.1 Enterprise Guide? Or is there something else for such Tasks?

    Regards,
    Andreas

    • Chris Hemedinger Chris Hemedinger
      Posted September 25, 2013 at 8:58 am | Permalink

      Andreas,

      Actually, I think that the 5.1 version of this task will work with 6.1 - simply follow the same installation instructions, but place in the ../6.1/Custom folder instead of the ../5.1/Custom folder.

      If it doesn't work for you, let me know.

      Chris

      • Posted September 27, 2013 at 1:37 am | Permalink

        Hi Chris,

        thanks a lot, we will install the 6.1 EG in November, so that's the time i can make a "real" try...

        Regards,
        Andreas

  4. James T
    Posted January 20, 2014 at 6:53 pm | Permalink

    Hi Chris, excellent task extension. I am having an issue with it in SAS EG 4.1 however.

    I am currently exporting a csv file with 65,000+ rows (280+ MB) from SAS to a network drive using the above method. Everything seems to go A-OK, however when you inspect the file after completion there are a substantial amount of blank spaces in the file. This happens apparently at random throughout the file, and it includes the header row.

    I have inspected the source data table, matched up a few specific rows and can confirm the data is there before export but not there after.

    Any idea what could be causing this?

    • Chris Hemedinger Chris Hemedinger
      Posted January 21, 2014 at 9:38 am | Permalink

      James,

      I'm not sure what's going on here, but you might experiment with another way to create the CSV file. For example, you could use DATA step with a FILE statement and PUT, or ODS CSV and PROC PRINT. This blog has some examples.

      Are you also using the Copy Files task to download the file from SAS to your PC (network drive)? This task for 4.1 (I think) simply does a binary file transfer. If your SAS session is UNIX and the destination is the PC, the line endings are treated differently. You might try opening the file in a text editor that can detect/convert these, such as TextPad or Notepad++.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>