how to transpose?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

how to transpose?

I have a row something like:

a          b          c          d

one     two     three     four

I need to transpose this into such:

a          b    

one     ou

two     ou

three   ou


Notice that I need to extract only a specific part of the value in d column: "ou".

Is this possible? How is is done with the whole value in column d? (without extracting anything, if it gets too tricky)


BR,

Paultrans


Accepted Solutions
Solution
‎03-14-2014 10:43 AM
PROC Star
Posts: 7,363

Re: how to transpose?

I think that the following will do what you want:

data have;

   informat instance $9.;

   input (a b c) ($) value1-value4 instance;

   cards;  

one two three 100 200 300 400 yr2013qr3

;

data want (drop=instance _: value1-value4);

  array values(*) value1-value4;

  set have;

  _quarter=input(catt(

       substr(instance,3,4),'Q',substr(instance,9,1)),yyq6.);

  do _i=0 to 3;

  value=values(_i+1);

    _qtr=intnx('quarter',_quarter,_i*-1,'b');

  quarter=substr(put(_qtr,yyq6.),5,2);

    year=year(_qtr);

    output;

  end;

run;

View solution in original post


All Replies
Respected Advisor
Posts: 4,649

Re: how to transpose?

Simple :

data have;

input a $ b $ c $ d $;

datalines;

one     two     three     four

;

data want(keep=a newb rename=newb=b);

set have;

newb = substr(d,2,2);

output;

a=b; output;

a=c; output;

run;

proc print data=want noobs; run;

PG

PG
Occasional Contributor
Posts: 15

Re: how to transpose?

It gets a little bit more tricky, I need to change it into the following:

a          b          c          value1     value2     value3     value4     instance    

one     two     three     100          200          300          400          yr2013qr3

I need to transpose this into such:

a          b         c          value     quarter     year    

one     two    three     100          Q3          2013

one     two    three     200          Q2          2013

one     two    three     300          Q1          2013

one     two    three     400          Q4          2012

So we need to extract the quarter and year information from 'instance', but notice that value4 is a quarter from previous year..

(so we want the last four quarters, starting from what 'instance' variable is specifying)

Paul

Super User
Super User
Posts: 7,401

Re: how to transpose?

Hi,

Its just a matter of pre-processing your data before transposing:

data have;
  attrib a b c format=$20. value format=best. quarter year format=$20.;   
  infile datalines;
  input a $ b $ c $ value quarter $ year $;
datalines;
one     two    three     100          Q3          2013
one     two    three     200          Q2          2013
one     two    three     300          Q1          2013
one     two    three     400          Q4          2012
;
run;
proc sort data=have;
  by a b c descending year descending quarter;
run;

data inter;
  set have;
  attrib instance format=$50.;
  retain instance;
  by a;
  if first.a then instance='yr'||strip(year)||'qr'||strip(tranwrd(quarter,'Q',''));
run;
proc transpose data=inter out=want prefix=value;
  by a b c instance;
  var value;
run;

Occasional Contributor
Posts: 15

Re: how to transpose?

Sorry if I wasn't clear, what I have is this:

a          b          c          value1     value2     value3     value4     instance    

one     two     three     100          200          300          400          yr2013qr3

and I want to transpose that into this below:

a          b         c          value     quarter     year    

one     two    three     100          Q3          2013

one     two    three     200          Q2          2013

one     two    three     300          Q1          2013

one     two    three     400          Q4          2012

Super User
Super User
Posts: 7,401

Re: how to transpose?

Hi,

Ok, please just reverse the logic then:

proc transpose data=want out=inter;
  by a b c instance;
  var col1 col2 col3 col4;
run;

data want;
  set inter;
  attrib year format=4. quarter format=$4. qnum format=best.;
  retain qnum;
  year=input(substr(instance,3,4),4.);
  if _name_="COL1" then do;
    qnum=input(strip(substr(instance,9,1)),best.);
  end;
  else do;
    qnum=qnum-1;
    if qnum=0 then do;
      year=year-1;
      qnum=4;
    end;
  end;
  quarter="Q"||put(qnum,1.);
run;

Occasional Contributor
Posts: 15

Re: how to transpose?

For some reason I just dont't seem to get it working..

Super User
Super User
Posts: 7,401

Re: how to transpose?

Did you run the first program I posted, then the second program?  In the second program I use the want from the first program as input.

Super User
Super User
Posts: 6,500

Re: how to transpose?

That is actually easier, or at least clearer to me. Now you just have the situation where you want to transpose VALUE1-VALUE3.  Only wrinkle you have added is that you also want to parse instance.

data want ;

  set have ;

   array x value1-value4 ;

  quarter = 'Q' || substr(instance,length(instance));

  year = substr(instance,3,4);

  do over x ;

    value = x;

    output;

  end;

  drop instance value1-value4 ;

run;

PROC Star
Posts: 7,363

Re: how to transpose?

:  I'm going to stick with my suggested code (3/14 10:43am).  As I interpreted the OP's have and want, the first record output is supposed to reflect the quarter and year listed, along with value1.  Then, the other 3 records are supposed to show values2 thru value 4, respectively, and each reflecting a quarter and year that is one less than the previous record output.

Super User
Super User
Posts: 6,500

Re: how to transpose?

Didn't catch that.

Solution
‎03-14-2014 10:43 AM
PROC Star
Posts: 7,363

Re: how to transpose?

I think that the following will do what you want:

data have;

   informat instance $9.;

   input (a b c) ($) value1-value4 instance;

   cards;  

one two three 100 200 300 400 yr2013qr3

;

data want (drop=instance _: value1-value4);

  array values(*) value1-value4;

  set have;

  _quarter=input(catt(

       substr(instance,3,4),'Q',substr(instance,9,1)),yyq6.);

  do _i=0 to 3;

  value=values(_i+1);

    _qtr=intnx('quarter',_quarter,_i*-1,'b');

  quarter=substr(put(_qtr,yyq6.),5,2);

    year=year(_qtr);

    output;

  end;

run;

Occasional Contributor
Posts: 15

Re: how to transpose?

Thanks, exactly what i want! thanks to others also..

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 527 views
  • 3 likes
  • 5 in conversation