topic Re: Rename Excel template then export to newly renamed file in SAS Programming

Rename Excel template then export to newly renamed file

texasmfp — Fri, 09 Apr 2021 06:26:20 GMT

Hi:

I am hoping there is programming language that will save me some steps. I want to take an existing excel template file ("&ROOT\CHOICES__template.xlsx") either rename it or create a copy with a new filename "&ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate"

to be followed by an export to the newly-renamed XLSX file:

PROC EXPORT DATA=COMPANY.&RESPONDENT._&SEGMENT._&STAGE._matcha
OUTFILE= "&ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate"
DBMS=XLSX REPLACE;
SHEET="CHOICES";
RUN;

Thanks

Re: Rename Excel template then export to newly renamed file

andreas_lds — Fri, 09 Apr 2021 06:19:35 GMT

Maybe i don't understand the process you try to create, but proc export will overwrite the existing file. So the first step seems to be redundant.

Re: Rename Excel template then export to newly renamed file

texasmfp — Fri, 09 Apr 2021 06:29:13 GMT

Thanks. The “existing” file does not yet exist, at least not with the customized name.

Re: Rename Excel template then export to newly renamed file

andreas_lds — Fri, 09 Apr 2021 06:57:27 GMT

@texasmfp wrote:

Thanks. The “existing” file does not yet exist, at least not with the customized name.

After copying the template file, it hopefully exists as &ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate. Afterwards proc export will overwrite the before copied file, hence you can skip the copy-step in your process.

Re: Rename Excel template then export to newly renamed file

texasmfp — Fri, 09 Apr 2021 07:23:56 GMT

Perhaps I am not explaining myself clearly, because that is exactly what I am trying to automate. I do not want to manually copy the excel template and rename it as &ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate

I want SAS to do that so that each time it creates a unique name. The template is not a blank file.

For example, today I run it and the file name resolves to

ACME_FINAL_BASE1_CHOICES__09APR21.xlsx

But, if I run it tomorrow, I want the name to be

ACME_FINAL_BASE1_CHOICES__10APR21.xlsx

OR, if I run it for a different company, it would be

AJAX_FINAL_BASE1_CHOICES__09APR21.xlsx

Re: Rename Excel template then export to newly renamed file

texasmfp — Fri, 09 Apr 2021 07:32:18 GMT

Essentially, I want to do two things: write a dataset to an existing Excel template and save that with a new name (or in reverse, create a copy of an existing excel template with a new name, and then write a SAS dataset to that new file). The new filename is populated with macros variables that can resolve differently on different days or with different company names or segment names, etc.... I can write to a blank Excel file and then manually copy that into a template and then rename the template. Its a pain. I am hoping there is a way to automate this in SAS. Thanks

Re: Rename Excel template then export to newly renamed file

ChrisNZ — Fri, 09 Apr 2021 08:56:20 GMT

Use function fcopy() to copy the template, then export your data to the new file.

Re: Rename Excel template then export to newly renamed file

Kurt_Bremser — Fri, 09 Apr 2021 09:18:00 GMT

Use FCOPY() to create the copy. Then, define a LIBNAME XLSX for the copy, and use a DATA step or PROC COPY to create the new sheet within the workbook file.

Re: Rename Excel template then export to newly renamed file

ballardw — Fri, 09 Apr 2021 14:34:13 GMT

@texasmfp wrote:

Perhaps I am not explaining myself clearly, because that is exactly what I am trying to automate. I do not want to manually copy the excel template and rename it as &ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate

I want SAS to do that so that each time it creates a unique name. The template is not a blank file.

For example, today I run it and the file name resolves to

ACME_FINAL_BASE1_CHOICES__09APR21.xlsx

But, if I run it tomorrow, I want the name to be

ACME_FINAL_BASE1_CHOICES__10APR21.xlsx

OR, if I run it for a different company, it would be

AJAX_FINAL_BASE1_CHOICES__09APR21.xlsx

Proc export does not "write to a template", it does not "fill in cells" or any manner of those things. Proc EXPORT creates files. If there is a file of the existing name it completely replaces it. There is no "template" information left. Period. You need an approach other than Proc Export for generating your output.

Re: Rename Excel template then export to newly renamed file

Sajid01 — Fri, 09 Apr 2021 15:24:23 GMT

As I understand it, you have an xlsx file with predefined formats, macros etc. You want the new file to use this preexisting file as the template and create a new one.

In my opinion , your code should

(1) make a copy the template excel workbook to a new copy with the desired name

(2) use DDE to populate data into the new workbook (see item 9 in https://support.sas.com/kb/32/455.html )

SAS needs to be running on windows for this to work.

Re: Rename Excel template then export to newly renamed file

ballardw — Fri, 09 Apr 2021 16:12:53 GMT

@Sajid01 wrote:

As I understand it, you have an xlsx file with predefined formats, macros etc. You want the new file to use this preexisting file as the template and create a new one.

In my opinion , your code should

(1) make a copy the template excel workbook to a new copy with the desired name

(2) use DDE to populate data into the new workbook (see item 9 in https://support.sas.com/kb/32/455.html )

SAS needs to be running on windows for this to work.

Unfortunately DDE is a very fragile approach to any solution even in the Windows operating system. The communication links that DDE relies on have been "hijacked" or similar by other fairly common programs. If you have Cisco Jabber running DDE will not work until the Jabber processes are Killed (not close the windows but go to Task Manager and Kill the running process). Other programs are likely doing the same. Microsoft doesn't maintain or even recommend DDE solutions anymore. So behavior of any program using DDE is subject to change.

Also some of the commands that require specifying file types change depending on the version of Office installed and are also subject to change without notice.

Re: Rename Excel template then export to newly renamed file

texasmfp — Fri, 09 Apr 2021 23:09:32 GMT

That is not true: You can specify a sheet name in the PROC Export and, it only replaces what is in that sheet. I do it all the time:

PROC EXPORT DATA=COMPANY.&RESPONDENT._&SEGMENT._&STAGE._matcha
OUTFILE= "&ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate"
DBMS=XLSX REPLACE;
SHEET="CHOICES";
RUN;

Re: Rename Excel template then export to newly renamed file

texasmfp — Fri, 09 Apr 2021 23:16:08 GMT

Kurt, I tried that and it destroyed the template. It was a .3 MB excel file before and a 1 kb excel file after that won't open in Excel.

Here is the code I used, which came from a SAS, which I adopted from https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarget=n10dz22b5ixohin1vwzilweetek0.htm&locale=en :

/* Set MSGLEVEL to I to write messages from FCOPY to the log. */
options msglevel=i;

filename src 'E:\SAS Data\CHOICES__template.xlsx' recfm=n;
filename dest 'E:\SAS Data\CHOICES__template2.xlsx' recfm=n;

   /* Create an example file to copy. */
data _null_;
   file src;
   do i=1, 2105, 300312, 400501;
     put i:words256.;
   end;
run; 

data _null_;
   length msg $ 384;
   rc=fcopy('src', 'dest');
   if rc=0 then
      put 'Copied SRC to DEST.';
   else do;
      msg=sysmsg();
      put rc= msg=;
   end;
run;

Re: Rename Excel template then export to newly renamed file

texasmfp — Fri, 09 Apr 2021 23:16:57 GMT

Chris, I tried that and it destroyed the template. It was a .3 MG excel file beforte and a 1 kb excel file after that won't open in Excel.

/* Set MSGLEVEL to I to write messages from FCOPY to the log. */
options msglevel=i;

filename src 'E:\SAS Data\CHOICES__template.xlsx' recfm=n;
filename dest 'E:\SAS Data\CHOICES__template2.xlsx' recfm=n;

   /* Create an example file to copy. */
data _null_;
   file src;
   do i=1, 2105, 300312, 400501;
     put i:words256.;
   end;
run; 

data _null_;
   length msg $ 384;
   rc=fcopy('src', 'dest');
   if rc=0 then
      put 'Copied SRC to DEST.';
   else do;
      msg=sysmsg();
      put rc= msg=;
   end;
run;

Re: Rename Excel template then export to newly renamed file

texasmfp — Sat, 10 Apr 2021 01:46:03 GMT

Resolved the code. Works flawlessly. Thanks to all for the tips.

/* Set MSGLEVEL to I to write messages from FCOPY to the log. */
options msglevel=i;

filename src '&ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate\CHOICES__template.xlsx' recfm=n;
filename dest "&ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate..xlsx" recfm=n;
 
data _null_;
   length msg $ 384;
   rc=fcopy('src', 'dest');
   if rc=0 then
      put 'Copied SRC to DEST.';
   else do;
      msg=sysmsg();
      put rc= msg=;
   end;
run;

PROC EXPORT DATA=COMPANY.&RESPONDENT._&SEGMENT._&STAGE._matcha
            OUTFILE= "&ROOT\&RESPONDENT._&SEGMENT._&STAGE._CHOICES__&sysdate"
            DBMS=XLSX REPLACE;
     SHEET="CHOICES";
RUN;

Re: Rename Excel template then export to newly renamed file

ChrisNZ — Sat, 10 Apr 2021 06:29:44 GMT

You can use outfile=DEST if you want, since you already have a filename pointer.

Re: Rename Excel template then export to newly renamed file

Patrick — Mon, 12 Apr 2021 07:34:28 GMT

@Sajid01

I strongly suggest to read this blog and then re-think DDE usage.