topic Re: How to export dataset to xlsx with copy files task in SAS Programming

How to export dataset to xlsx with copy files task

msf2021 — Tue, 18 Feb 2025 19:29:36 GMT

Hello everyone!

So I cannot export a sas dataset using code (no permissions, not really sure why, company policies) but i found out that i can use the task copy files to export a dataset in txt with no problems so basically what i do is:

Create a program:

/* Data to export */
%let lib  = WORK;
%let datafile = filename;
%let save_path= mypath

/* Local folder to download to */ 
%let download_to = &save_path;
 
/* detect proper delim for UNIX vs. Windows */
%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));
 
%let download_from =
  %sysfunc(getoption(work))&delim.&datafile..txt;
 
filename src "&download_from.";
 
proc export data=&lib..&datafile.
	dbms=dlm 
	file=src
	replace;
	delimiter=';';
run;
 
filename src clear;

and then use the copy files like this :

I want to be able to do the same but now export as xlsx instead of txt

Can anyone help me please?

Re: How to export dataset to xlsx with copy files task

ChrisHemedinger — Wed, 19 Feb 2025 13:27:26 GMT

The Copy Files task simply downloads your file from the SAS server file system, whatever its type. So instead of creating a text file, you need to create an XLSX file to download.

You can create an XLSX by changing your PROC EXPORT code from DBMS=DLM to DBMS=XLSX. This does require SAS/ACCESS to PC Files to be licensed on the server.

%let download_from = %sysfunc(getoption(work))&delim.&datafile..xlsx; filename src "&download_from."; proc export data=&lib..&datafile. dbms=xlsx file=src replace; run;

Re: How to export dataset to xlsx with copy files task

Tom — Tue, 18 Feb 2025 22:12:29 GMT

Delimiter??? Why would you use the DELIMITER statement when making an XLSX file? Those do not use delimiters.

Re: How to export dataset to xlsx with copy files task

msf2021 — Wed, 19 Feb 2025 09:28:14 GMT

Maybe i didn't explain myself clear. The code i posted is to export txt that's why the delimiter. It's an example code because with that code i can export a txt from sas to my computer. What i need is a similar approach to export xlsx

Re: How to export dataset to xlsx with copy files task

Patrick — Wed, 19 Feb 2025 10:07:14 GMT

@msf2021 wrote:

Maybe i didn't explain myself clear. The code i posted is to export txt that's why the delimiter. It's an example code because with that code i can export a txt from sas to my computer. What i need is a similar approach to export xlsx

@msf2021

The only bit you need to change in your working code is Proc Export so it creates a Excel file instead of a Text file.
@ChrisHemedinger already shared the code with you. I assume he copy/pasted your Proc Export code and then modified it for Excel output ...and just missed to remove the delimiter statement.

Re: How to export dataset to xlsx with copy files task

ChrisHemedinger — Wed, 19 Feb 2025 13:28:07 GMT

The only bit you need to change in your working code is Proc Export so it creates a Excel file instead of a Text file.
@ChrisHemedinger already shared the code with you. I assume he copy/pasted your Proc Export code and then modified it for Excel output ...and just missed to remove the delimiter statement.

Whoops, yes. Fixed that!

Re: How to export dataset to xlsx with copy files task

Tom — Wed, 19 Feb 2025 18:14:14 GMT

You explained fine, I was responding to Chris who should know better.

If your existing program + task structure works for text files it should work the same for XLSX files. (Note: If the copy files task as an option to download binary files differently than text file, like FTP does, you need to make sure to copy the files as binary.)

Just make sure to use XLSX as the extension on the filename and use XLSX as the DBMS= option of PROC EXPORT. And of course remove the DELIMITER= statement as it makes no sense for XLSX files. And you can make the program simpler as there is no need to make a fileref to use PROC EXPORT, just specify the filename directly in the FILE= option.

/* Data to export */
%let lib  = WORK;
%let datafile = filename;
%let save_path= mypath

/* Local folder to download to */ 
%let download_to = &save_path;
 
/* detect proper delim for UNIX vs. Windows */
%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));
 
%let download_from =
  %sysfunc(getoption(work))&delim.&datafile..xlsx;
 
proc export data=&lib..&datafile.
  dbms=XLSX
  file="&download_from."
  replace
;
run;

Re: How to export dataset to xlsx with copy files task

yabwon — Wed, 19 Feb 2025 18:25:30 GMT

How about, instead exporting, use the XLSX libname?

libname x XLSX "R:/text.xlsx";

data x.sheet1;
  set sashelp.class;
run;

data x.sheet2;
  set sashelp.cars;
run;

Bart

Re: How to export dataset to xlsx with copy files task

ChrisHemedinger — Wed, 19 Feb 2025 18:43:29 GMT

@Tom wrote:

If your existing program + task structure works for text files it should work the same for XLSX files. (Note: If the copy files task as an option to download binary files differently than text file, like FTP does, you need to make sure to copy the files as binary.)

Good point! The Copy Files task has an option to treat as text and fix line-endings (Unix vs Windows). The default is a binary transfer, which saves on encoding hassles. You'll want to check the settings in your task within the project.