BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zhangda
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

3 REPLIES 3
Reeza
Super User

You talk about columns but show rows of data. 

How exactly do you want your output to be?

zhangda
Fluorite | Level 6

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

Patrick
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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