Help using Base SAS procedures

how to put the latest date as the first

Accepted Solution Solved
Reply
Contributor
Posts: 74
Accepted Solution

how to put the latest date as the first

[ Edited ]

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;


Accepted Solutions
Solution
‎10-24-2016 02:11 AM
Respected Advisor
Posts: 3,894

Re: how to put the latest date as the first

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;

View solution in original post


All Replies
Super User
Posts: 17,840

Re: how to put the latest date as the first

You talk about columns but show rows of data. 

How exactly do you want your output to be?

Contributor
Posts: 74

Re: how to put the latest date as the first

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

Solution
‎10-24-2016 02:11 AM
Respected Advisor
Posts: 3,894

Re: how to put the latest date as the first

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 233 views
  • 0 likes
  • 3 in conversation