SAS

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

by  •  • Comments Off on How to use SAS DATA step to copy a file from anywhere

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.

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.

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.