topic Re: how to put the latest date as the first in SAS Procedures

how to put the latest date as the first

zhangda — Mon, 24 Oct 2016 00:01:07 GMT

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;

Re: how to put the latest date as the first

Reeza — Sun, 23 Oct 2016 22:52:25 GMT

You talk about columns but show rows of data.

How exactly do you want your output to be?

Re: how to put the latest date as the first

zhangda — Mon, 24 Oct 2016 00:01:55 GMT

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

Re: how to put the latest date as the first

Patrick — Mon, 24 Oct 2016 02:03:00 GMT

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;