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

[RichardAD]

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.

[Kurt_Bremser · Accepted solution]

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

[RichardAD]

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

[Kurt_Bremser]

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

[Ksharp]

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;

[mkeintz]

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;

[Tom]

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