topic Re: Proc COPY, can you prevent WARNING: Engine XLSX does not support SORTEDBY operations in SAS Procedures

Proc COPY, can you prevent WARNING: Engine XLSX does not support SORTEDBY operations

RichardAD — Fri, 27 Oct 2023 15:30:42 GMT

I have a general macro that is copying data sets from a library to workbook.

libname workbook xlsx 'c:\temp\sample.xlsx';
proc sort data=sashelp.class out=work;
  by name;
run;
proc copy in=work out=workbook;
 select class;
run;

Will log

NOTE: Copying WORK.CLASS to WORKBOOK.CLASS (memtype=DATA).
WARNING: Engine XLSX does not support SORTEDBY operations.  SORTEDBY information cannot be copied.
NOTE: There were 19 observations read from the data set WORK.CLASS.
NOTE: The data set WORKBOOK.CLASS has 19 observations and 5 variables.
NOTE: The export data set has 19 observations and 5 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

I really don't care about this and would like to prevent the WARNING: because a log checker program further in the system will do all sorts of alerts on any kind of warning. The ecosystem I'm in does not allow for alteration the data sets, nor copying them to a different location (to remove the SORTEDBY meta data) before copying to workbook.

Re: Proc COPY, can you prevent WARNING: Engine XLSX does not support SORTEDBY operations

Kurt_Bremser — Fri, 27 Oct 2023 15:41:18 GMT

Use the COPY statement in PROC DATASETS, and specify the NOWARN option in the PROC statement.

Re: Proc COPY, can you prevent WARNING: Engine XLSX does not support SORTEDBY operations

RichardAD — Fri, 27 Oct 2023 18:58:47 GMT

Unfortunately, the answer sounded good but turns out to be incorrect for the question at hand.

From Proc DATASETS documentation

NOWARN

suppresses the warning when used with the FORCE option to concatenate two data sets with different variables.

Also, from Proc DATASETS, COPY Statement documentation

Using the COPY Procedure Instead of the COPY Statement

Generally, the COPY procedure functions the same as the COPY statement in the DATASETS procedure. Here is a list of differences:

The IN= argument is required with PROC COPY. In the COPY statement, IN= is optional. If omitted, the default value is the libref of the procedure input library.
PROC DATASETS cannot work with libraries that allow only sequential data access.
The COPY statement honors the NOWARN option but PROC COPY does not.

There is no NOWARN option for the COPY statement, so I guess the doc is referring to the NOWARN of the DATASETS proc.

Regardless, the sample code here also logs WARNINGs despite using the option NOWARN.

libname workbook xlsx "c:\temp\sample.xlsx"; proc sort data=sashelp.class out=work.class; by name; proc sort data=sashelp.cars out=work.cars; by msrp; run; proc datasets nolist nowarn lib=work ; copy out=workbook ; select class cars ; run; quit;

Log

137673 proc sort data=sashelp.class out=work.class; by name; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: The data set WORK.CLASS has 19 observations and 5 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 137674 proc sort data=sashelp.cars out=work.cars; by msrp; 137675 run; NOTE: There were 428 observations read from the data set SASHELP.CARS. NOTE: The data set WORK.CARS has 428 observations and 15 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 137676 proc datasets nolist nowarn lib=work ; 137677 copy out=workbook ; 137678 select class cars ; 137679 run; NOTE: Copying WORK.CLASS to WORKBOOK.CLASS (memtype=DATA). WARNING: Engine XLSX does not support SORTEDBY operations. SORTEDBY information cannot be copied. NOTE: There were 19 observations read from the data set WORK.CLASS. NOTE: The data set WORKBOOK.CLASS has 19 observations and 5 variables. NOTE: The export data set has 19 observations and 5 variables. NOTE: Copying WORK.CARS to WORKBOOK.CARS (memtype=DATA). WARNING: Engine XLSX does not support SORTEDBY operations. SORTEDBY information cannot be copied. NOTE: There were 428 observations read from the data set WORK.CARS. NOTE: The data set WORKBOOK.CARS has 428 observations and 15 variables. NOTE: The export data set has 428 observations and 15 variables. 137680 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.08 seconds cpu time 0.00 seconds

Re: Proc COPY, can you prevent WARNING: Engine XLSX does not support SORTEDBY operations

Kurt_Bremser — Fri, 27 Oct 2023 20:06:17 GMT

Then I would try to replace PROC COPY with a simple DATA step, or use PROC EXPORT to create the sheet.

Re: Proc COPY, can you prevent WARNING: Engine XLSX does not support SORTEDBY operations

Ksharp — Sat, 28 Oct 2023 09:31:49 GMT

Try option SORTEDBY=NULL.

libname workbook xlsx 'c:\temp\sample.xlsx';
proc sort data=sashelp.class out=class(sortedby=_null_);
  by name;
run;
proc copy in=work out=workbook;
 select class;
run;

Re: Proc COPY, can you prevent WARNING: Engine XLSX does not support SORTEDBY operations

mkeintz — Sat, 28 Oct 2023 12:20:06 GMT

The SORTEDBY metadata for your dataset WORK is not inherited if you make a copy of work. So apply PROC COPY to that dataset:

libname workbook xlsx 'c:\temp\sample.xlsx';
proc sort data=sashelp.class out=work;
  by name;
run;

data workview/ view=workview;
  set work;
run;


proc copy in=workview out=workbook;
 select class;
run;

Re: Proc COPY, can you prevent WARNING: Engine XLSX does not support SORTEDBY operations

Tom — Sun, 29 Oct 2023 18:16:13 GMT

You could sort directly into the workbook. Then you get only a NOTE and not a WARNING.

1    libname workbook xlsx 'c:\downloads\sample.xlsx';
NOTE: Libref WORKBOOK was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: c:\downloads\sample.xlsx
2    proc sort data=sashelp.class out=workbook.class;
3      by name;
4    run;

NOTE: The XLSX engine does not support setting a sort indicator. The physical order of observations might not be preserved.
NOTE: Sort indicator cannot be set on WORKBOOK.class.DATA.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORKBOOK.class has 19 observations and 5 variables.
NOTE: The export data set has 19 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

To eliminate all of the notes just use a DATA step instead of a PROC to write to the workbook.

1    libname workbook xlsx 'c:\downloads\sample.xlsx';
NOTE: Libref WORKBOOK was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: c:\downloads\sample.xlsx
2    proc sort data=sashelp.class out=class;
3      by name;
4    run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


5    data workbook.class; set class; run;

NOTE: There were 19 observations read from the data set WORK.CLASS.
NOTE: The data set WORKBOOK.class has 19 observations and 5 variables.
NOTE: The export data set has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds