Last week I alluded to some very useful applications of the Copy Files task. This is one of them. If you have SAS Enterprise Guide 7.13 or later, the Copy Files task is in the Tasks->Data menu. In earlier versions, you'll have to download/install the task as a custom task.
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
63 Comments
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...
This is a very useful contribution. Thanks a lot for sharing this!
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
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
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
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?
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++.
Hi Chris, very useful thing. What could you say about copying of whole folder with this Add-In?
It's a good idea. Right now, the task supports wildcard notation, so you can copy all files in a folder with "*.*". But it does not support subfolders in this way. Part of the limitation is that, for now, the task does not create new folders at the destination. It can copy contents only into existing folders.
Thank you for quick response. Whether I can create directories for all files I want to copy (I'm going to do this using dcreate function) - can I use this add-in for copying from local PC to different direcrories on net drive "in one action", other words without multiple adding this add-in to process flow? I dont know how I can cycle eg process flow, and not sure if it is possible..
Antony, you can't "loop through" a series of directories with a single instance of the task. But you can create a "task template" of the operation, and easily re-use it throughout multiple projects (or the same project).
Here's how -- first, add the task with the values you need for one of the directories. Specify all of the options as you need them. Then you can run it or click the Save button in the task to save it in your project without running it immediately.
Then, right-click on the task in the process flow and select Create Task Template.... Assign a meaningful name to the template, such as "Copy PC files to Server". After you save the template, you'll find it in the Tasks->Task Templates menu or in the Task list window. When you select the template, it will be added to the project with all of your settings remembered. Simply tweak what you need to change for each instance. You'll still have several copies of the Copy Files task in your project, but the template will save you lots of time in defining them.
Hi all, Is there any SAS code to export the SAS programs from EG file? I have an Option of "Export Program as a step in Project" but i don't want to use that as it takes more time to export each file. Please let me know if any code is available.
Thank you
Shahikanth,
You can export all code in a project by using the automation object model and a scripting language like VB Script or PowerShell. Here is an article with several examples.
Chris,
Have you heard of any file size limitations? Your proc export sends the data to file=src where src is the fileref for the temporary work (i.e., %sysfunc(getoption(work)&delim.&datafile..csv). Could there be a problem if I a large table (~350 GB). Might I run out of space in the temporary directory?
Thank you!
Tomas,
350GB is a very large file to store in scratch space -- yes, you might run out of space. You'll need to work with your system administrator to identify a location that might be appropriate for this. You can always change the example code that I provided to point to whatever agreed-upon path you come up with.
But the download of a 350GB file is likely to be very slow-going -- over an hour on a Fast LAN connection. I don't think it's practical to move such large files around in this way.
Hi there
when I tried to download a SAS file 'HELP' using the above data export code I get the error message as shown here. Is it because I don't have proper authorization or something else ?
ERROR: Insufficient authorization to access D:\HELP.csv.
If you're using this exact code to test, it sounds like the &DOWNLOAD_FROM macro isn't building out properly. You should add a %PUT &DOWNLOAD_FROM.; statement in your code to see what the value is. In my test it shows as:
Hi Chris,
I have been attempting to get this solution to work but keeping running into the below error:
ERROR: Target folder (&download_to) does not exist or cannot be accessed on LIBP03P-7H3QRID
I have tried a few different destination folders and below is the code I am using when attempting to export to my desktop. Any ideas or is this user permission related? Thanks:
Is "LIBP03P-7H3QRID" the name of your local desktop machine? And did you click the "Resolve macro variables in source and destination paths" checkbox on the Copy Files task? It looks like the task is not substituting in the macro variable values properly.
In EG 5.1 we had the Run and Save buttons in this Custom Task. Now in EG 7.1 we don't have the Run and Save buttons. We just have OK, which will automatically run the task. I would like to get that Save option back. Can we do so with EG 7.1?
Here is the reply I got back from our SAS Support team when I posed this dilemma:
'We do not have any control over the custom task. This custom task is written while back; therefore, some of the features may not compatible with EG 7.1 so that the SAVE option is omitted from EG 7.1.'
Linda, the Run and Save button are in the 7.1 version of this task. Are you sure that you have the proper version installed? It should be SAS.Tasks.CopyFiles71.dll.
Is this feature available on EG 4.3? I can't find it on my EG 4.3. thanks.
Hmmm....I got it from another Chris's post. Thanks.
Glad you found it! You must have found the download for the Copy Files custom task.
Yes, I also installed it on my EG 4.3 and it works perfectly, thanks.
But I still have a question:
Is there any way to call this task or task template in SAS code or macro programmingly rather than clicking the menu manually? if so, it will make this feature much more flexible.
thanks.
Eric, glad it's working.
No, there isn't any SAS code that does the same operation, since the code runs on the SAS server and can't reach your local desktop.
You could use FILENAME FTP or network paths (\\server\folder) to accomplish file-copy operations in code, but often that's not an option in client-server environments.
Thanks Chris. Yes, ours is typical client-server environment. My scenario is: I run my SAS programs on our remote UNIX server via my EG 4.3 on my local Wins7, then the programs create some excel files there. What I am trying to do is to let the programs to copy or transfer these excel files back to my local machine. Is there any option to achieve this?
Not directly. Your best option would be to place them in a UNIX directory that you can also access from your PC. For example, at my work we have a mapped drive that can get to our UNIX home directories.
Thanks Chris. I got it.
Hi Chris,
I am having to read data from sharepoint site (http://sharepoint.umsmed.edu/research...), the following code is not recognizing the files for me. Am I pointing this correctly? It read the file ok if I specify the path for excel file in a local drive
options validvarname=any;
libname xl XLSX "http://sharepoint.umsmed.edu/research/JHS/JHSFC/CONA_12312014.xlsx";
proc datasets lib=xl; quit;
libname xl CLEAR;
Here is the warnig:WARNING: No matching members in directory.
Thanks for the help
Stanford,
You can't assign a LIBNAME to a remote URL like that. You need to download the file first into your SAS session, then read it. I documented this technique in this post about Dropbox -- but the same method can apply to your SharePoint case. So something like this:
filename _input "%sysfunc(getoption(work))/CONA_12312014.xlsx"; proc http method="get" url="http://sharepoint.umsmed.edu/research/JHS/JHSFC/CONA_12312014.xlsx" out=_input ; run; libname xl XLSX _input; proc datasets lib=xl; quit; libname xl CLEAR;Chris,
also having an issue saying:
WARNING: Apparent symbolic reference DOWNLOAD_FROM not resolved.
WARNING: Apparent symbolic reference DOWNLOAD_TO not resolved.
ERROR: Target folder (&download_to.) does not exist or cannot be accessed on USAZ29628W2C003
when I trace back I'm actually getting an warning in the original code line:
%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
full code I wrote was:
/* DATA to EXPORT */
%let lib = '/Retail_Services/mb28697/CSAT_SFT_FILES;
%let datafile = usan;
/* Local folder to download to */
%let download_to = Z:\IVR Reporting\THD NLU\IVR Survey Results\USAN Files;
/*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
replace;
putnames=no;
run;
filename src clear;
In the code you supplied, you left off a closing quote in this line:
%let lib = '/Retail_Services/mb28697/CSAT_SFT_FILES;You're missing a closing single quote at the end. Should be:
%let lib = '/Retail_Services/mb28697/CSAT_SFT_FILES';nm the previous comment. I'm acutally getting:
libname CSAT_FLD "/Retail_Services/mb28697/CSAT_SFT_FILES";
/* DATA to EXPORT */
%let lib = csat_fld;
%let datafile = usan;
%put &lib..&datafile.;
/* Local folder to download to */
%let download_to = Z:\IVR Reporting\THD NLU\IVR Survey Results\USAN Files;
/*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=csat_fld.usan
dbms=csv
replace;
putnames=no;
run;
filename src clear;
ERROR: FILE= or TABLE= is required and must be specified.
Hi, you left off the OUTFILE= option in PROC EXPORT, which tells SAS the name of the file to save. Add OUTFILE=&download_from., like this:
proc export data=csat_fld.usan OUTFILE=&download_from. dbms=csv replace; putnames=no; run;Hi Chris, I modified the code in an attempt to export multiple SAS data sets that reside in my work directory to a single excel file, creating a worksheet for each dataset.
I receive an error message stating ERROR: "WORK." is not a valid name.
22: LINE and COLUMN cannot be determined.
%let lib = work;
%let datafile = %str(*);
/* Local folder to download to */
%let download_to = H:\PPACA Reports;
/* detect proper delim for UNIX vs. Windows */
%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));
%let download_from = %sysfunc(getoption(work))&delim.%str(*).xlsx;
/* file to create in step */
filename src "%sysfunc(getoption(work))&delim.&datafile..xlsx";
Proc Export data=&lib..&datafile.
dbms=xlsx
file=src
replace;
Run;
Janet, while the Copy Files task can process wildcards like *, PROC EXPORT cannot. The way your code is written now, you're trying to do:
PROC EXPORT DATA=WORK.*
...
That's not going to work. You'll need to use a SAS macro to iterate through each data member in WORK and have a PROC EXPORT step with a SHEET= option.
Then, your datafile value should be %str(*).xlsx, to download just the one XLSX file to your PC.
Alternatively, you might be able to use LIBNAME XLSX to add multiple data sets to a single workbook. Requires SAS 9.4M2 or later.
Thanks for your prompt response Chris!
Unfortunately I don't have SAS 9.4 - that looks like an easy solution.
I did add a macro to identify/iterate the Work datasets, but I'm getting an error message associated with the &inMember. I have 3 datasets in work and the error message returns for each dataset. Example of error message:
NOTE: Line generated by the macro variable "INMEMBER".
67 WORK.RX_CLAIMS
______________
22
201
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.
ERROR 201-322: The option is not recognized and will be ignored.
This is my complete code:
%let filedate=&sysdate;
%put filedate=&filedate;
/*get libname and dataset name of data to export*/
PROC SQL;
CREATE TABLE DATASET_LIST AS
SELECT libname, memname as member
FROM dictionary.tables
WHERE libname='WORK'
and memname not in ("_PRODSAVAIL","DATASET_LIST");
QUIT;
%macro ExportReports();
%local datasetCount iter inLibref inMember;
/*get number of datasets*/
proc sql noprint;
select count(*) into :datasetCount
from dataset_list
quit;
/*Initiate loop*/
%let iter=1;
%do %while (&iter. <= &datasetCount);
/*get libref and dsn for dataset for this iteration*/
data _NULL_;
set DATASET_LIST (firstobs=&iter. obs=&iter.);
/*write libname and dataset name to macro variables*/
call symput("inLibref", strip(libname));
call symput("inMember",strip(member)); /*strip fixes trailing blanks*/
run;
%let download_to = 'H:\PPACA Reports';
%let download_from = &inLibref..&inMember;
%let datafile = _&filedate..xlsx;
Proc Export data=&datafile.
dbms=xlsx
file=&inLibref..&inMember.
replace;
sheet=&inMember.;
Run;
/*increment the iterator of the loop*/
%let iter=%eval(&iter.+1);
%end;
%mend;
/*call the macro*/
%ExportReports()
So close! I've created a simpler version that works.
/* create some sample data */ data class; set sashelp.class; data cars; set sashelp.cars; run; %let filedate=&sysdate; %let download_to = 'c:\temp\PPACA Reports'; %let download_from = %sysfunc(getoption(WORK))/_&filedate..xlsx; %macro ExportReports(); /*get number of datasets*/ proc sql noprint; select memname into :member1 - :member9999 FROM dictionary.tables WHERE libname='WORK' and memname not in ("_PRODSAVAIL"); %let datasetCount = &sqlobs.; quit; /*Initiate loop*/ %do iter=1 %to &datasetCount; Proc Export data=WORK.&&member&iter. dbms=xlsx file="&download_from." replace; sheet="&&member&iter."; Run; %end; %mend; /*call the macro*/ %ExportReports()It works!! Hurray!!
Last of all - the copy/files task doesn't like the &download_to macro, I removed the space in the download to file name but that didn't matter. Also tried single quotes instead of double and that didn't work either. I'm using a virtual machine (thus the reference below to V7333). If I hard-code the file path it works, but using the macro i receive this error message:
ERROR: Target folder ("c:\temp\PPACA_Reports") does not exist or cannot be accessed on V7333
This is resolved :) I removed the quotes from the &download_to path and the path is now recognized. Thanks again for your assistance with the macro!
Hi Chris,
I came across this functionality and it helps a lot in transferring files from server to my computer. However, I need to download multiple files from different folders with lots of sub-folders in a SAS Unix server to my computer. Is there anyway I can tweak this program to copy files in different folders?
Thanks,
Ben
Hi Ben,
No, I'm afraid not. The task supports just one source folder and doesn't follow into subfolders, so you would need to have multiple instances of the task to handle each distinct source directory. With the Task Template feature, you can save yourself time by creating a cookie-cutter task that you can apply quickly -- but you still need to have one per folder.
Chris,
Is there a way to create CSV files all at once?
I mean to read the &download_from macro to read multiple SAS datasets?
Thanks Sunny
Certainly! Instead of naming a specific file, you can use wildcards in your file specification, like "%sysfunc(getoption(work))&delim.*.csv" to pull ALL of the .CSV files from the Work folder.
Chris: How to import excel file from sharepoint into SAS EG work folder?
Sometimes Sharepoint is also set up as a network share (ex: \\myshare.company.com\documents\...), so that might be an option. Otherwise, you might have to try using PROC HTTP or FILENAME WebDAV methods to access the SharePoint content.
Hi Chris,
Does it not work for .XLS files. Its giving error. Though its working fine for XLSX
If you're using DBMS=XLS, that creates a file that uses a very old format for Excel. XLSX is definitely the preferred format to use.
How to export to multiple sheets in the same excel using the sheet= option? Its overwriting the original excel. If I uncheck the overwrite option, then its not writing at all. More precisely, if I have an existing excel and I only want to write to a particular sheet but keep other sheets unmodified can we do that
PROC EXPORT can write multiple sheets, and can update an existing spreadsheet with additional sheets (or replace an existing sheet) without creating a new file. You can see an example in this blog post about ODS Excel and PROC EXPORT.
Hi Chris,
Can I use this functionality to copy a SAS table to my desktop in .sas7bdat format? I have been able to copy them into my work directory from my computer, but I am getting errors trying to copy them out to my computer.
Thanks!
For this, it's better to use the Download SAS Data Sets task. It's built into EG.
Is that the task that is named "Download Data Files to PC..."?
Yes! There is a Download Data Files, and an Upload Data Files -- both deal in sas7bdat files, fetching from a SAS library or adding to a SAS library.
Hi Chris,
When I get to the "Copy Files" input box, the "Destination folder on" is not linking to my desktop, so it does not recognize the file path that I plugged into the macro. Do you know how I can change that to my local device?
Are you running in a terminal server/Citrix? This might not map to your local machine.
Thanks for the quick reply! Yes, it's setup through a remote connection. Do I have any options?
Not really, unless you can map an area/folder on your local machine that your remote terminal can "see". If you're using Remote Desktop there are some options you might be able to tweak in the connection -- related to Local Resources, Local devices & resource, More... you'll see an option for Drives.
Hi Chris,
I'm using SAS EG 8.1 version. after adding the wildcard [%let download_from = %sysfunc(getoption(work))&delim.%str(*).csv;] and create file [filename src "%sysfunc(getoption(work))&delim.&datafile..csv";] code. My code doesn't work, i have tired to modified the code to download few datasets from Work.
/* Data to export */
%let lib = WORK;
%let datafile = %str(*);
/* Local folder to download to */
%let download_to = C:\Documents\New_folder;
/* detect proper delim for UNIX vs. Windows */
%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));
%let download_from =
%sysfunc(getoption(work))&delim.%str(*).xlsx;
filename src "%sysfunc(getoption(work))&delim.&datafile..xlsx";
proc export data=&lib..&datafile.
dbms=xlsx
file=src
replace;
run;
filename src clear;
It looks like you are trying to use a wildcard specification for the output of PROC EXPORT -- that won't work. You need to export to a specific single file -- PROC EXPORT can manage just one file at a time.
Hi Chris, We have pdf files that were made in SAS (UNIX) that we want to export to Windows. Since the proc export procedure DBMS= doesn't allow pdf, is there another method to export these pdf?
If using SAS Enterprise Guide to connect to a Unix SAS session, you can use the Copy Files task to include this "download" into your process flow. You can download any file from your SAS session, including your PDF. If you're not using SAS Enterprise Guide and need this to work in pure SAS code, then either you need a common shared network location that you can reach from both Unix and Windows, or you could use something like FTP to move the files across. (The SAS FILENAME statement has an FTP method...usable if you have an FTP server to connect to.)