topic proc transpose ERROR: Data set WORK.DIAGS is not sorted in ascending sequence. The current BY in SAS Programming

proc transpose ERROR: Data set WORK.DIAGS is not sorted in ascending sequence. The current BY

qwerty12 — Sat, 10 Dec 2022 14:15:43 GMT

code :

data diags;
set nicecode.diag;
run;

proc transpose data=diags out=work.d(keep=clm_no diag1-diag25) prefix=diag;
by clm_no ;
id seq_no ;
var code;
run;

By running this code I got below error:

So I modified the code as below:

data diags;
set diag;
run;

proc sort data = diags;
by clm_no;
run;

proc transpose data=diags out=d(keep=clm_no diag1-diag25) prefix=diag;
id seq_no ;
var code;
run;

Then I got below Error:

Here is the diag Table :

total record counts in diag table - 585546644

Re: proc transpose ERROR: Data set WORK.DIAGS is not sorted in ascending sequence. The current BY

ger15xxhcker — Sat, 10 Dec 2022 14:24:55 GMT

It looks like you have duplicate values for the ID variable, seq_no, in your input dataset. You should be able to resolve this issue by using the nodupkey option on your PROC SORT step, as follows:

proc sort data = diags nodupkey;
by clm_no;
run;

Re: proc transpose ERROR: Data set WORK.DIAGS is not sorted in ascending sequence. The current BY

ballardw — Sun, 11 Dec 2022 02:54:59 GMT

You second proc transpose code does not have the BY clm_no. Why did you remove that after sorting by that variable?

If you have any duplicates of ID variables when there is no BY grouping then that is expected. If you have duplicate values of ID variables within a BY group, i.e. value of Clm_no, then that would also be expected. The ID variables are used to create names for output variables. SAS will not allow a data set to have a variable name duplicated. So if the value if an ID variable, or combination of Id variables. occurs, you get this error because the requested variable names are invalid.

Since this is a matter of values of variables then a picture of variable types isn't particularly helpful. You would need to provide an actual example of data values.

Note: you can test if you might have this sort of repeated Id value but using Proc freq code:

Proc freq data=diags;
   table clm_no * seq_no /missing list;
run;

If you see a count of 2 or greater for any clm_no * seq_no combination then you need to do something else that the Transpose code you show.