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.
35 Comments
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
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.
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.
Pingback: Copy a file using a SAS program: another method - The SAS Dummy
how can we use proc http to post excel file to web site using URL ?
The exact approach will be determined by the site/API that the website is supporting, but you could try something of this form:
The important bits are: a fileref to the file you're sending, METHOD='POST', and the CT (mime type) for the file.
Thank you
Many thanks Chris. Very useful! Am updating a monthly code so will incorporate this!
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.
Of course, after a bit of thought I realised, if you can upload your unzipped file to an external URL location, you can use file in url to ovrcome the 10MB limitation. Very useful indeed.
Sarah, glad you have it figured out. Some comments on this post might also be of help -- regarding reading/writing ZIP files with SAS.
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 :)
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.
Thanks for very useful code.
One question: Is this also possible to copy to an existing workbook by creating a new sheet in the existing workbook?
Not with this method, but you might be able to use LIBNAME XLSX or PROC EXPORT DBMS=XLSX to achieve this.
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.
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
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"';
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.;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
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.
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.
Two other ways you can try, both discussed in this other blog post. I'd try the simpler FCOPY function first (don't forget recfm=n on the FILENAME statements).
Fantastic! Just when I though that I have to switch to python to do these things you show me that it can all be done in good old SAS. Thank you so much!
Thank you Chris.
Does this code work to read an excel file from SFTP? It works when I run it to copy excel files locally but when the source filename is from SFTP, the file comes corrupted from the SFTP server with following message. "The file format or file extension is invalid."
Here is my code.
filename in sftp 'abc.xlsx' host="hostname" user="test" CD="&SFTPPath." options="-P 5555-i D:\PrivateKey.ppk -pw test" ;
filename out "D:\test\abc.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;
RUN;
Have you tried the FCOPY function in SAS 9.4? This might work better and provide a simpler approach.
Hi Chris,
Thanks for below program, its very useful to me. I have one question, if we have 2 files and need to concatenate and copy to some other path. Can you please let me know the logic.
/* 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;
This program copies just one file. If you need to concatenate two together, I suggest doing the concatenate work first...then copy the resulting file. Your example shows Excel files. To combine those, you would need to read each into SAS as data sets (using PROC IMPORT or LIBNAME XLSX), combine using DATA step or PROC SQL, and then use PROC EXPORT or ODS EXCEL to create a single XLSX file that you would move.
I tried this method to copy an Excel file from one Windows shared directory to another. The log showed no errors, but nothing was copied. Am I misunderstanding the purpose?
In general I'd expect this to work. But, if not -- I'd try the FCOPY function next. If that works, it's a simpler approach supported in more recent versions of SAS.
Hi. I tried this code to copy the file byte by byte. but result file being generated as blank file.
Approach I tried is:
data cars;
set sashelp.cars;
run;
%let l_workpath=&path;
libname va_rpt "&l_workpath";
data va_rpt.cars;
set cars;
run;
filename fileexp "&l_workpath/cars.sas7bdat";
data _null_;
fid = fopen ('fileexp');
filesize = finfo(fid, 'file size (bytes)');
put 'NOTE: ' filesize;
call symputx('contentln',filesize);
byte_range= filesize-1;
call symputx('byte_range',byte_range);
rc= fclose (fid);
run;
filename in "&l_workpath/cars.sas7bdat";
filename out "&l_workpath/cars_part1.sas7bdat";
/* 196608 */
/* 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 i=196608 to 0 by -1;
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;
filename fileexp1 "&l_workpath/cars_part1.sas7bdat";
data _null_;
fid = fopen ('fileexp1');
filesize = finfo(fid, 'file size (bytes)');
put 'NOTE: ' filesize;
call symputx('contentln',filesize);
byte_range= filesize-1;
call symputx('byte_range',byte_range);
rc= fclose (fid);
run;