In general, if a large table is reduced a little by COMPRESS, the PROC SORT applied to it does not improve much, even with the TAGSORT or PRESORTED options. At this point what should be done to reduce the processing time of a proc sort?   this is the result respectively without COMPRESS and with COMPRESS applied to table tab1   proc sort data = tab1 out = tab_out(keep=cod_source cod_compagnia cod_contratto num_posizione cod_garanzia cod_tipo_riserva dt_effetto_riserva ts_inizio_validita ts_fine_validita imp_riserva cod_divisa dt_carico cod_tipo_agg_riserva) TAGSORT; by cod_source cod_compagnia cod_contratto num_posizione cod_garanzia cod_tipo_riserva dt_effetto_riserva ts_fine_validita; run; NOTE: Tagsort reads each observation of the input data set twice. NOTE: The data set tab_out has 301087776 observations and 13 variables. NOTE: PROCEDURE SORT ha utilizzato (tempo totale di elaborazione): real time 7:19.48 user cpu time 4:35.45 system cpu time 44.12 seconds memory 20087284.06k OS Memory 20111168.00k Timestamp 21/04/2023 02:51:28 p. Step Count 17 Switch Count 693   proc sort data = tab1 out = tab_out(keep=cod_source cod_compagnia cod_contratto num_posizione cod_garanzia cod_tipo_riserva dt_effetto_riserva ts_inizio_validita ts_fine_validita imp_riserva cod_divisa dt_carico cod_tipo_agg_riserva); by cod_source cod_compagnia cod_contratto num_posizione cod_garanzia cod_tipo_riserva dt_effetto_riserva ts_fine_validita; run; NOTE: There were 301087776 observations read from the data set tab1. NOTE: The data set tab_out has 301087776 observations and 13 variables. NOTE: PROCEDURE SORT ha utilizzato (tempo totale di elaborazione): real time 8:14.81 user cpu time 2:47.42 system cpu time 1:03.64 memory 31707434.82k OS Memory 31729708.00k Timestamp 21/04/2023 03:03:25 p. Step Count 19 Switch Count 613       ... View more

Good morning. is there a code to check if there is a lock on a sas table? that is, if it is already open. Thank you ... View more

I don't quite understand why a proc sort applied to a large table worsens performance if applied to the same dataset, but compressed. The table in question weighs about 29Gb (305819687 observations and 16 variables, of which 13 variables used in the "by"), and by doing the COMPRESS the weight is reduced to only 26Gb. A first doubt I have is that usually compress reduces the occupied memory much more. I wish I could optimize this proc sort but I didn't get good results, neither with the use of compress for the table, nor with the Bufsize= and Bufno = options (I tried them for all possible combinations), nor with the TAGSORT option . With the SORTSIZE option I think the situation does not change because this is set to 32Gb, therefore a value already higher than the table size. Here is the outcome of the log of the starting sort: NOTE: There were 305819687 observations read from the data set ODS_VT.T_F_PTFVT_RISERVA_AWARDS. NOTE: The data set WORK.ETLS_SORTEDXREF has 305819687 observations and 13 variables. NOTES: PROCEDURE SORT used (total processing time): real-time 8:29.88 user cpu time 2:35.53 system cpu time 53.34 seconds memory 31708967.57k OS Memory 31729708.00k Timestamp 04/05/2023 08:18:18 m. Step Count 15 Switch Count 659 and here the result of the log after the application of the proc sort on the compressed table with the use of the TAGSORT option: NOTE: Tagsort reads each observation of the input data set twice. NOTE: The data set WORK.ETLS_SORTEDXREF has 305819687 observations and 13 variables. NOTES: Compressing data set WORK.ETLS_SORTEDXREF decreased size by 7.66 percent. Compressed is 311022 pages; uncompressed would require 336806 pages. NOTES: PROCEDURE SORT used (total processing time): real time 12:35.03 user cpu time 6:48.25 system cpu time 1:22.71 memory 20403259.93k OS Memory 20426884.00k Timestamp 05/04/2023 08:35:33m. Step Count 19 Switch Count 1205 ... View more