How to sort numeric variables based on parts of the values

[Greg_P]

I have a data set made up of many 3 digit values which represent dates, where the first digit is the term in a school year (Assuming there are only 6 terms in a year) and the 2nd two digits are the year: 185 = 1st semester of 1985:

185, 186, 285, 286, 385, 390, etc.

I want to sort them by the last 2 digits of each number (year), then the first digit (semester):

185, 285, 385, 186, 286, 390, etc.

How can I sort the data/dates to be ordered like that?

[PeterClemmensen]

Are these numeric or character values?

[Greg_P]

Numeric

[PeterClemmensen · Accepted solution]

This should do

data have;
input x @@;
datalines;
185 186 285 286 385 390
;

proc sql;
   create table want as
   select x from have
   order by mod(x, 100),
           int(x / 100);
quit;

[RichardDeVen]

You can use Proc SQL to reorder data according to a custom sort key

" sort them by the last 2 digits of each number (year), then the first digit (semester) "

In this example the ORDER BY uses:

• first key: mod(number,100) to compute the yy part
• second key: the number itself -- no need to perform int(number/100) because that computation would be the same order as the number

data have;
  do semester = 1 to 4;
  do year = 1985 to 2020;
    num = semester * 100 + mod(year,100);
    output;
  end;
  end;
  keep num;
run;

proc sql;
  create table have as
  select * from have
  order by mod(num,100), num
  ;
quit;

[FreelanceReinh]

To avoid a return of the Y2K problem, you may want to add a suitable shift to the values, e.g.

order by mod(x+50,100), x

for a date range from 1950 through 2049.

[Tom]

Why not first re-code the values into something that is both easier to read and easier to sort?

So if you old variable was named NUM you could make a new variable called CHAR.  So take the last two digits and convert it into an actual year (always represent years with four digits to avoid confusion).  Then you can append the first digit as a suffix.  So now the values should sort and be less confusing.

data have;
  input num @@;
cards;
185 186 285 286 385 390
;

data want;
  set have ;
  length char $6 ;
  year = mod(num,100);
  if year < 30 then year=2000+year;
  else year=1900+year;
  char=catx('-',year,int(NUM/100));
  drop year;
run;

proc sort;
  by char;
run;

proc print;
run;

Obs    num     char

 1     185    1985-1
 2     285    1985-2
 3     385    1985-3
 4     186    1986-1
 5     286    1986-2
 6     390    1990-3