topic Re: proc export, dbms=xlsx - unexpected result when newfile=yes in SAS Programming

proc export, dbms=xlsx - unexpected result when newfile=yes

xxformat_com — Sun, 17 May 2020 17:04:52 GMT

Hi,

When using proc export, could it be that newfile=yes is not working with dbms=xlsx?

The following code add a second tab to an existing excel file

rather than deleting the first excel file and creating a new one with one two.

It works as expected with dbms=excel tho.

Best Regards,

filename demo "&xxdemo./reporting/class.xlsx";

proc export data    = sashelp.class
            dbms    = xlsx
            outfile = demo;
     sheet='All Students'n;
run;

filename demo;

filename demo "&xxdemo./reporting/class.xlsx";

proc export data    = sashelp.class (where=(sex='M'))
            dbms    = xlsx /*excle*/
            outfile = demo;
     newfile = y;
     sheet='Male'n;
run;

filename demo;

Re: proc export, dbms=xlsx - unexpected result when newfile=yes

ghosh — Sun, 17 May 2020 18:27:19 GMT

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 70         
 71         filename demo "~/dat/class.xlsx";
 72         
 73         proc export data    = sashelp.class
 74                     dbms    = xlsx
 75                     outfile = demo;
 76              sheet='All Students'n;
 77         run;
 
 NOTE: The export data set has 19 observations and 5 variables.
 NOTE: "DEMO" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.01 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              2471.43k
       OS Memory           29532.00k
       Timestamp           05/17/2020 06:18:55 PM
       Step Count                        24  Switch Count  0
       Page Faults                       0
       Page Reclaims                     831
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           16
       
 
 78         
 79         filename demo;
 NOTE: Fileref DEMO has been deassigned.
 80         
 81         filename demo "~/dat/class.xlsx";
 82         
 83         proc export data    = sashelp.class (where=(sex='M'))
 84                     dbms    = xlsx /*excle*/
 85                     outfile = demo;
 86              newfile = y;
 87              sheet='Male'n;
 88         run;
 
 NOTE: The export data set has 10 observations and 5 variables.
 NOTE: "DEMO" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.01 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              2814.96k
       OS Memory           30044.00k
       Timestamp           05/17/2020 06:18:55 PM
       Step Count                        25  Switch Count  0
       Page Faults                       0
       Page Reclaims                     552
       Page Swaps                        0
       Voluntary Context Switches        21
       Involuntary Context Switches      0
       Block Input Operations            16
       Block Output Operations           16
       
 
 89         
 90         filename demo;
 NOTE: Fileref DEMO has been deassigned.
 91         
 92         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

It works for me on SODA (SAS On Demand for Academics) . I am able to open the Excel file and view the contents in Windows 10

Re: proc export, dbms=xlsx - unexpected result when newfile=yes

Kurt_Bremser — Sun, 17 May 2020 18:56:04 GMT

This is documented behavior. SAS will create a new spreadsheet; if the Excel file already exists, no new file is created, but a new sheet within the file. If the sheet already exists, and no REPLACE option is given, the sheet is not overwritten, and you will be notified.

Re: proc export, dbms=xlsx - unexpected result when newfile=yes

xxformat_com — Sun, 17 May 2020 19:48:08 GMT

I can see the content of the file. It is just the one I was expected. I was expecting the same one as with dbms=excel.

Re: proc export, dbms=xlsx - unexpected result when newfile=yes

xxformat_com — Sun, 17 May 2020 19:49:44 GMT

I could not find the documentation where newfile=yes was not working with dbms=xlsx. Would you have the link?

Re: proc export, dbms=xlsx - unexpected result when newfile=yes

Kurt_Bremser — Sun, 17 May 2020 20:17:52 GMT

See :

Microsoft Excel Workbook Files

(scroll down to NEWFILE=):

Restriction Available only for DBMS=XLS for backward compatibility.

Re: proc export, dbms=xlsx - unexpected result when newfile=yes

xxformat_com — Sun, 17 May 2020 20:21:01 GMT

Thank you Kurt