BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BellGuy
Calcite | Level 5

Warning: The total length of all variables in the BY list must be less than  or equal to 4092 bytes. The host sort cannot be used. The internal SAS sort will be used; this may impact performance.

I am using SAS 9.1 on z/OS.

I am using a macro which includes a sort step 26 times.

This message appears after the sixth iteration.

Since I am sorting the same fields from 1 to 6, why would the length increase?

1 ACCEPTED SOLUTION

Accepted Solutions
LarryWorley
Fluorite | Level 6

Following up with Terry's comment and the log contents:

The length is most probably referring the amount storage consumed by the by variables values.

I only see the by list listed once -- for the last iteration. Some questions to look at:

1.  Is the same by list used on the first 6 iterations?

2.  What are the lengths of the five by variables -- OSEQ PPN PPR CPN CPR -- in the last dataset, TOOL7.N7OUT ;  You can do a proc contents to find this out.

3.  What are the lengths of the by variables in the other tables?

What are the sources of the output tables?  If from a single sas dataset or single table in a database, I would not expect any differences in length.

But if they are separate datasets or are being created from Excel or CVS files, the lengths in the sas data sets could vary from file to file.

If your goal is only to eliminate the warning message, just use the system option sortpgm=SAS.  Otherwise, you need to look at the data.

BTW, if your host sort is SYNCSORT, it has a limit of 128 control fields, with a total length of 4092.  I believe that by list variables are basically control fields.

View solution in original post

5 REPLIES 5
LarryWorley
Fluorite | Level 6

Do you know that the macro is using the same sort bylist on each step?  Is it possible that the macro is taking the list you provided and using different parts of the list in each iteration; perhaps adding a field from the provided by list each iteration.

Try rerunning with 'options mprint symbolgen ;'  This might let you see specifically what is happening with the by list.  Once you can determine the variables in iteration 6, determine the total length of those variables -- 8 bytes for numeric plus the sum of lengths of the character variables.

Hopefully you will find a reason for the long length of the by variables which occurs at iteration 6 but not at iteration 5.

Try this diagnosis and let us know what you see.

Good luck.

BellGuy
Calcite | Level 5

Still not finding anything. Here is the sysout, if this helps:

187        %ITRATE(P0,N1,N1OUT,1,P1);RUN;                           

NOTE: Table TOOL1.N1OUT created, with 65 rows and 23 columns.       

MPRINT(ITRATE):   PROC SORT;                                        

MPRINT(ITRATE):  BY OSEQ PPN PPR CPN CPR;                           

SYMBOLGEN:  Macro variable D resolves to 1                          

SYMBOLGEN:  Macro variable C resolves to N1OUT                      

NOTE: There were 65 observations read from the data set TOOL1.N1OUT.

NOTE: The data set TOOL1.N1OUT has 65 observations and 23 variables.

NOTE: The PROCEDURE SORT used 0.00 CPU seconds and 15722K.          

                                                                     

189        %ITRATE(P1,N2,N2OUT,2,P2);RUN;                           

NOTE: Table TOOL2.N2OUT created, with 28 rows and 23 columns.       

NOTE: There were 28 observations read from the data set TOOL2.N2OUT.

NOTE: The data set TOOL2.N2OUT has 28 observations and 23 variables.

NOTE: The PROCEDURE SORT used 0.00 CPU seconds and 16978K.          

                                                                     

191        %ITRATE(P2,N3,N3OUT,3,P3);RUN;                           

NOTE: Table TOOL3.N3OUT created, with 826 rows and 23 columns.      

NOTE: There were 826 observations read from the data set TOOL3.N3OUT.

NOTE: The data set TOOL3.N3OUT has 826 observations and 23 variables.

NOTE: The PROCEDURE SORT used 0.01 CPU seconds and 18002K.           

                                                                      

193        %ITRATE(P3,N4,N4OUT,4,P4);RUN;                            

NOTE: Table TOOL4.N4OUT created, with 6973 rows and 23 columns.      

NOTE: There were 6973 observations read from the data set TOOL4.N4OUT.

NOTE: The data set TOOL4.N4OUT has 6973 observations and 23 variables.

NOTE: The PROCEDURE SORT used 0.27 CPU seconds and 16982K.           

                                                                      

195        %ITRATE(P4,N5,N5OUT,5,P5);RUN;                            

NOTE: Table TOOL5.N5OUT created, with 108 rows and 23 columns.       

NOTE: There were 108 observations read from the data set TOOL5.N5OUT.

NOTE: The data set TOOL5.N5OUT has 108 observations and 23 variables.

NOTE: The PROCEDURE SORT used 0.01 CPU seconds and 17494K.           

                                                                      

197        %ITRATE(P5,N6,N6OUT,6,P6);RUN;                            

NOTE: Table TOOL6.N6OUT created, with 106 rows and 23 columns.       

NOTE: There were 106 observations read from the data set TOOL6.N6OUT.

NOTE: The data set TOOL6.N6OUT has 106 observations and 23 variables.

NOTE: The PROCEDURE SORT used 0.01 CPU seconds and 18262K.          

199        %ITRATE(P6,N7,N7OUT,7,P7);RUN;                            

NOTE: Table TOOL7.N7OUT created, with 392 rows and 23 columns.       

MPRINT(ITRATE):   PROC SORT;                                         

MPRINT(ITRATE):  BY OSEQ PPN PPR CPN CPR;                            

SYMBOLGEN:  Macro variable D resolves to 7                           

SYMBOLGEN:  Macro variable C resolves to N7OUT                       

                                                                      

WARNING: The total length of all variables in the BY list must be less

          than or equal to 4092 bytes. The host sort cannot be used.  

          The internal SAS sort will be used; this may impact         

          performance.                                                

NOTE: There were 392 observations read from the data set TOOL7.N7OUT.

WARNING: Host sort not available; SAS sort was used.                 

NOTE: The data set TOOL7.N7OUT has 392 observations and 23 variables.

NOTE: The PROCEDURE SORT used 0.06 CPU seconds and 29654K.          

Since I am always sorting 23 variables, I fail to see why the length would change. Any help would be greatly appreciated.

terryfearn
Calcite | Level 5

The length may refer to the size of the variable. If there are character variables, the length can change with the size of the text contained in the variable. That would make the message data dependent, thus it could be generated with the same code from different datasets.

LarryWorley
Fluorite | Level 6

Following up with Terry's comment and the log contents:

The length is most probably referring the amount storage consumed by the by variables values.

I only see the by list listed once -- for the last iteration. Some questions to look at:

1.  Is the same by list used on the first 6 iterations?

2.  What are the lengths of the five by variables -- OSEQ PPN PPR CPN CPR -- in the last dataset, TOOL7.N7OUT ;  You can do a proc contents to find this out.

3.  What are the lengths of the by variables in the other tables?

What are the sources of the output tables?  If from a single sas dataset or single table in a database, I would not expect any differences in length.

But if they are separate datasets or are being created from Excel or CVS files, the lengths in the sas data sets could vary from file to file.

If your goal is only to eliminate the warning message, just use the system option sortpgm=SAS.  Otherwise, you need to look at the data.

BTW, if your host sort is SYNCSORT, it has a limit of 128 control fields, with a total length of 4092.  I believe that by list variables are basically control fields.

BellGuy
Calcite | Level 5


Thank you Larry,

The proc contents did the trick.

Even though I was reading in a fixed length variable, it was reassigned to a new variable without a length declaration.

OSEQ was read in:

INPUT @1 OSEQ $155.;

It was reassigned:

NSEQ=SUBSTR(OSEQ,1,6)||PUT(CNT,Z5.)||SUBSTR(OSEQ,12,144);

Subsequently, it was renamed in preparation for the next iteration:

RENAME NSEQ=OSEQ;

Thanks for your prompt response.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1373 views
  • 1 like
  • 3 in conversation