How to use SAS DATA step to copy a file from anywhere

28

SAS-based processes are critical to many organizations, but sometimes the trickiest part of your job falls into one or both of these activities:

  • Getting stuff from the outside world "into" SAS. (Once it's in SAS, as many of you know, the world is your oyster.)
  • Getting the output of your SAS process "out" to the non-SAS people who need to consume it.

Here's a handy DATA step program that can copy file content from one place to another. It copies the content byte-by-byte, so it's great for copying files from your SAS session to a place outside of SAS, or vice versa.

/* these IN and OUT filerefs can point to anything */
filename in "c:\dataIn\input.xlsx"; 
filename out "c:\dataOut\output.xlsx"; 
 
/* copy the file byte-for-byte  */
data _null_;
  length filein 8 fileid 8;
  filein = fopen('in','I',1,'B');
  fileid = fopen('out','O',1,'B');
  rec = '20'x;
  do while(fread(filein)=0);
     rc = fget(filein,rec,1);
     rc = fput(fileid, rec);
     rc =fwrite(fileid);
  end;
  rc = fclose(filein);
  rc = fclose(fileid);
run;
 
filename in clear;
filename out clear;

It's true that you can copy disk-based files from one place to another by using operating system shell commands (via SYSTASK, for example). But the cool thing about the above program is that it can copy files to/from other places as well -- any location that you can access with a FILENAME statement, including URLs. For example, imagine that there is a file on the Web that you want to bring into SAS for analysis. Simply use FILENAME URL to define the IN fileref. Here's an example that grabs an Excel file from the Web and imports it into SAS:

filename in url "http://www.LotsOfData.org/data/data.xlsx"
  /* PROXY= is important for going outside firewall, if you have one */
  /* proxy="http://yourProxy.company.com" */
  ;
filename out "c:\temp\data.xlsx";
data _null_;
 length filein 8 fileid 8;
 filein = fopen('in','I',1,'B');
 fileid = fopen('out','O',1,'B');
 rec = '20'x;
 do while(fread(filein)=0);
  rc = fget(filein,rec,1);
  rc = fput(fileid, rec);
  rc =fwrite(fileid);
 end;
 rc = fclose(filein);
 rc = fclose(fileid);
run;
 
/* Works on 32-bit Windows */
/* If using 64-bit SAS, you must use DBMS=EXCELCS */
PROC IMPORT OUT= WORK.test 
  DATAFILE = out /* the downloaded copy */
  DBMS=EXCEL REPLACE;
  SHEET="FirstSheet";
  SCANTEXT=YES;
  USEDATE=YES;
  SCANTIME=YES;
  GETNAMES=YES; /* not supported for EXCELCS */
  MIXED=NO; /* not supported for EXCELCS */
RUN;
 
filename in clear;
filename out clear;

Or, going the other way, perhaps you have a SAS stored process that creates a file that you want to position as a "download" file when the user runs the stored process in a web browser. In that case, you can use the reserved fileref _WEBOUT instead of the OUT fileref. For a specific example of this in action, see this tip about working with the Stored Process Web Application.

Share

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies.  He also hosts the SAS Tech Talk webcasts each year from SAS Global Forum, connecting viewers with smart people from SAS R&D and the impressive work that they do.

Related Posts

28 Comments

  1. Interesting and useful as always.

    Presumably copying data at a bit level increases the chances of a bit being corrupted on the way. Is there anything clever that SAS can do to use redundancy and a check sum to detect/correct corruptions?

    Thanks,
    Tom

  2. Tom, that's a good question. When copying a file this way, I think you're on your own to verify integrity. You might be able to do something clever with a hash or maybe the MD5 function in SAS to verify a before-and-after state...if you have a copy of the file in the "before" state or some record to compare it to.

  3. Nice!
    I just used this to get around the fact that printer ODS destinations can't use URL-based images, by copying the image from its url location to a local file, then pointing to that file in the appropriate ODS statement.

  4. Pingback: Copy a file using a SAS program: another method - The SAS Dummy

    • Chris Hemedinger
      Chris Hemedinger on

      The exact approach will be determined by the site/API that the website is supporting, but you could try something of this form:

      filename myxls "c:\data\data.xls";
      filename results TEMP;
      
      proc http
        url="http://mysite.company.com/add-excel.php"  
        in=myxls
        out=results
        ct="application/vnd.ms-excel"
        method='POST'
        ;
      run;
      

      The important bits are: a fileref to the file you're sending, METHOD='POST', and the CT (mime type) for the file.

  5. Very, very useful Chris, thank you!. This is something I use all the time in my 'home programming language'.

    Could this be used to unzip the contents of a zipped sas7bdat do you think? On the University edition and can't use shell commands or upload over 10MB.


    .
    /* these IN and OUT filerefs can point to anything */
    filename zipfile ZIP "/folders/myfolders/Coursework/demo.zip" member="demo.sas7bdat";
    filename in zipfile;
    filename out "/folders/myfolders/Coursework/demo.sas7bdat";

    Throws an error. I've only just started learning, so sorry if this is obvious. Any advice appreciated.

  6. Hi Chris - Thanks for the useful code but during the process we loose the timestamp of the original file , is there any where to retain that while copying .. may be asking too much :)

    • Chris Hemedinger
      Chris Hemedinger on

      I've actually seen it work both ways. I think it might depend on where you're copying from and maybe the operating system, as the byte-by-byte method can preserve even the file attributes.

      But really only the OS-level file copy will be sure to keep those file attributes (like time stamp) intact.

      • In the one hand I'm able with Libname XLSX and PROC EXPORT, but my challenge is, that I don't want to lose the formatted spreadsheet from the excel file. What I'm looking for is something like the byte-by-byte copy. So I keep the formatted file and move it to another place, but off course it ovverrides the existing workbook. I'm interested to move a created spreadsheet from workbook A to a existing workbook B. Can you imagine if this is possible anyhow? I really appreciate all thoughts.

  7. Hi I also want to copy or send the report I create in to some other folder. But my report name will be static always but with the report name there will be a date string and it keeps changing according to the run date and while copying the report to anther folder I dont want the date string.How I can achieve this .Please advice me

    • Chris Hemedinger
      Chris Hemedinger on

      If you're using this method with the DATA step, you can alter the FILENAME OUT name by parsing/stripping out the date portion of the name. Use the SCAN function and/or SUBSTR to get just the portion of the name before the appended date. I'd need to see an example to give a specific code sample, but supposing the date portion is a predictable 8 characters at the end of the name, you could use SUBSTR to create a truncated version of the name for output.

      Alternatively, you can copy the file as-is and then use the SAS rename function to rename it.

      • I was using an X statement to achive the results .Please see the code below. It is giving an error while copying the files like cannot find the specified path
        Below the code

        %let rptpath = 'R:\CDW Reporting\Ad-Hoc\WR#_JAS0031 -Walls Regional Hospital Hainey\Data Out\';
        %let rptname = 'Walls Regional Hospital Hainey Report';
        %let rptdate = today();
        %let rptdatefmt = YYMMDDd10.;
        %let path=&rptpath;

        %put &report;

        %let path=&rptpath;
        %let name=&rptname ;
        %let date=&rptdate;
        %let format=&rptdatefmt;

        X'COPY "&path\&name || ' ' || put(&date,&format).xlsx" "C:\Users\UCS1MKP\Desktop\y\&rptname.xlsx"';

        • Chris Hemedinger
          Chris Hemedinger on

          I suggest breaking up the command into pieces, and using the CAT function to build it up. Check your work in a temp text file so that you can see the command that you've assembled.

          Note that you can deal with doublequotes in the text by "escaping" them with an additional doublequote symbol. That is, "" will resolve to " in your command value.

          %let rptpath = R:\CDW Reporting\Ad-Hoc\WR#_JAS0031 -Walls Regional Hospital Hainey\Data Out; %let rptname = Walls Regional Hospital Hainey Report; %let rptdate = today(); %let rptdatefmt = YYMMDDd10.; filename scratch "c:\temp\scratch.txt"; data _null_; file scratch; length cmd $ 255; cmd = cat("COPY ", "'&rptpath\&rptname", %sysfunc(putc(&rptdate,&rptdatefmt)),".xlsx' ", " ""C:\Users\UCS1MKP\Desktop\y\&rptname..xlsx"" "); put cmd; /* put command in macro var */ call symput('cpycmd',cmd); run; X &cpycmd.;

  8. Walter Earl Roper on

    Is there such a way to copy a url with videos about a technical subject to a 'depository' where the info can be 'stored'??

    W

    • Chris Hemedinger
      Chris Hemedinger on

      Walter, do you mean the videos themselves? Copy the video files from the web to a local copy? Or just metadata about the video? I do some work with video metadata and our video hosting service (using their APIs). But I'm not sure what you're after.

  9. Hi Chris,

    This blog is really helpful. However I am facing issues in the following scenario.
    I am trying your mentioned code to move or copy an excel file from our SAS UNIX server to my local machine. I am running my program from Desktop SAS. I am connecting to UNIX SAS using FILENAME SFTP method, but I am getting the following issue:

    NOTE: remote:<>/Test_excel.xlsx => local:C:\Users\jrozario\AppData\Local\Temp\SEG5496\SAS Temporary
    Files\_TD12932_D9X1T32_\#LN00050psftp>
    2 The SAS System 10:27 Thursday, September 14, 2017

    Obsolete.
    NOTE: DATA statement used (Total process time):
    real time 9.25 seconds
    cpu time 0.03 seconds

    44
    45 PROC IMPORT OUT= WORK.test
    46 DATAFILE = out /* the downloaded copy */
    47 DBMS=xlsx REPLACE;
    48 RUN;

    ERROR: Error opening XLSX file -> <>\sample1.xlsx . It is either not an Excel spreadsheet or it is damaged.
    Error code=8014900A
    Requested Input File Is Invalid
    ERROR: Import unsuccessful. See SAS Log for details.
    NOTE: The SAS System stopped processing this step because of errors.

    Please find the updated SAS code which I am using:

    filename in sftp '<>/Test_excel.xlsx' options="-i <>\privatekey.ppk" user=jrozario
    host='<>' options="-v" lrecl=32767 debug;
    filename out "<>\sample1.xlsx";

    /* copy the file byte-for-byte */
    data _null_;
    length filein 8 fileid 8;
    filein = fopen('in','I',1,'B');
    fileid = fopen('out','O',1,'B');
    rec = '20'x;
    do while(fread(filein)=0);
    rc = fget(filein,rec,1);
    rc = fput(fileid, rec);
    rc =fwrite(fileid);
    end;
    rc = fclose(filein);
    rc = fclose(fileid);
    run;

    PROC IMPORT OUT= WORK.test
    DATAFILE = out /* the downloaded copy */
    DBMS=xlsx REPLACE;
    RUN;

    filename in clear;
    filename out clear;

    Please let me know if I am doing anything wrong here. Thanks in advance.

Leave A Reply

Back to Top