How to export dataset to xlsx with copy files task

[msf2021]

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?

[ChrisHemedinger]

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;

[Tom]

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

[msf2021]

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

[Patrick]

---

@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.

[ChrisHemedinger]

---

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!

[Tom]

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;

[ChrisHemedinger]

---

@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.

[yabwon]

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