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?
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;
Are these numeric or character values?
Numeric
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;
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:
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;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.