Rename Excel template then export to newly renamed file

[texasmfp]

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

[andreas_lds]

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.

[texasmfp]

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

[andreas_lds]

---

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

[texasmfp]

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

[texasmfp]

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

[ChrisNZ]

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

[texasmfp]

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.

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&docsetTarg... :

/* 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;

[ballardw]

---

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

[texasmfp]

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;

[Kurt_Bremser]

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.

[texasmfp]

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&docsetTarg... :

/* 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;

[Sajid01]

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.

[ballardw]

---

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

[texasmfp]

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;