how to put the latest date as the first

[zhangda]

Hello,

I want to rearrange the year column as you see below, in the order as the lastest date as first, for instance, Q3_2016, Q2_2016, Q1_2016, Q4_2015, Q3_2015, Q2_2015. One thing is that, the quarter keeps rolling, that means, when we are at the beginning of 2017, I will have Q4_2016 rolled into the dataset.  then how to make it? Thanks a lot!

data old;
input year $ resp ;
datalines;
Q1_2015 0.35
Q2_2015 0.55
Q3_2015 0.65
Q4_2015 0.75
Q1_2016 0.47
Q2_2016 0.58
Q3_2016 0.69
;run;

proc transpose data=old out=new;
id year;
var resp ;
run;

[Reeza]

You talk about columns but show rows of data. 

How exactly do you want your output to be?

[zhangda]

I just added a new piece of code to clarify my problem, sorry for confusing you.

[Patrick · Accepted solution]

You need to use a variable which sorts the way you want it in your ID statement.

Your YEAR variable is character and therefore all Q1... values will be before Q2...

Below an approach which should give you what you want.

data old;
  input year $ resp;
  datalines;
Q1_2015 0.35
Q2_2015 0.55
Q3_2015 0.65
Q4_2015 0.75
Q1_2016 0.47
Q2_2016 0.58
Q3_2016 0.69
;
run;

data need(drop=fmtname start label) cntlin(keep=fmtname start label);
  set old;
  format dt yyq6.;
  dt=input(substr(year,4,4)||substr(year,1,2),yyq6.);
  
  retain fmtname 'myQuarters';
  start=dt;
  label=year;
run;

proc format cntlin=cntlin;
run;

proc transpose data=need out=new;
  id dt;
  format dt myQuarters.;
  var resp;
run;