transpose info

[davide]

Hi,

i need to transpose 3 variables by 1 key

the only one way that i thought if to make 3 proc transpose and after merge the 3 data set togheter

but i think that exist something easier, can u help me?

this is an example of the data that i m managing

col1 col2 col3 col4

A 2014 N 0

A 2013 X 1

A 2012 N 0

A 2011 X 2

B 2013 X 5

B 2012 X 0

B 2011 N 1

B 2010 N 0

...

what i would create is :

A 2014 N 0 2013 X 1 2012 N 0 2011 X 2

B                 2013 X 5 2012 X 0 2011 N 1 2010 N 0

...

thanks

Davide

[RW9]

Yep, transpose and merge.  You could also do it with retained arrays and output last from group:

data have;

  attrib col1 col2 col3 col4 format=$20.;

  infile datalines;

  input col1 $ col2 $ col3 $ col4 $;

datalines;

A 2014 N 0

A 2013 X 1

A 2012 N 0

A 2011 X 2

B 2013 X 5

B 2012 X 0

B 2011 N 1

B 2010 N 0

;

run;

data want (drop=ind j;

  set have;

  array results{4,3} $200.;   /* Assuming a 4*3 grid */

  array dat{3} col2-col4;

  by col1;

  retain ind results1-results12;

  if first.col1 then ind=1;

  do j=1 to 3;

    results{ind,j}=dat{j};

  end;

  ind=ind+1;

  if last.col1 then output;

run;

[Loko]

Hello,

Assuming the data is sorted upon col1:

data have;
input col1 $ col2 col3 $ col4;
datalines;
A 2014 N 0
A 2013 X 1
A 2012 N 0
A 2011 X 2
B 2013 X 5
B 2012 X 0
B 2011 N 1
B 2010 N 0
;
run;

proc sql noprint;
select min(col2), max(col2)
into :miny, :maxy from have;
quit;

data want;

array vars2{&miny:&maxy} y%sysfunc(strip(&miny))-y%sysfunc(strip(&maxy));
array vars3{&miny:&maxy} $ z%sysfunc(strip(&miny))-z%sysfunc(strip(&maxy));
array vars4{&miny:&maxy} w%sysfunc(strip(&miny))-w%sysfunc(strip(&maxy));

do until (last.col1);
set have;
by col1;
vars2{col2}=col2;
vars3{col2}=col3;
vars4{col2}=col4;
end;

drop col2-col4;
run;

[Ksharp]

How about this :

data have;
  attrib col1 col2 col3 col4 format=$20.;
  infile datalines;
  input col1 $ col2 $ col3 $ col4 $;
datalines;
A 2014 N 0
A 2013 X 1
A 2012 N 0
A 2011 X 2
B 2013 X 5
B 2012 X 0
B 2011 N 1
B 2010 N 0
;
run;

proc sql;
create table temp as
 select b.*,col3,col4 
  from have  a right  join (select * from (select distinct col1 from have),(select distinct col2 from have)     ) b
   on a.col1=b.col1 and a.col2=b.col2;
quit;

data temp;
 set temp;
 by col1;
 if first.col1 then n=0;
 n+1;
run;
data _null_;
 set temp(keep=col1 n) end=last;
 if _n_ eq 1 then call execute('data want(drop=n);merge ');
 call execute('temp(where=(col1="'||strip(col1)||'" and n='||strip(n)||') rename=(col2=col2'||strip(n)||' col3=col3'||strip(n)||' col4=col4'||strip(n)||'))');
 if last then call execute(';by col1;run;');
run;

Xia Keshan

[davide]

Thanks a lot

Davide

[TomKari]

I'm a little late to the party, but here's another one. I'm not that good with arrays, so the fourth step can probably be tidied up.

Tom

data have;
   input col1 $ col2 col3 $ col4;
   cards;
A 2014 N 0
A 2013 X 1
A 2012 N 0
A 2011 X 2
B 2013 X 5
B 2012 X 0
B 2011 N 1
B 2010 N 0
run;

data inter1;
   set have;
   dataval = catx('09'x, put(col2,4.), col3, put(col4, 1.));
   keep col1 col2 dataval;
run;

proc transpose data=inter1 out=inter2(drop=_name_) prefix=In;
   by col1;
   id col2;
   var dataval;
run;

data want;
   array InVars $  In2014 In2013 In2012 In2011 In2010;
   array OutYears  Yr2014 Yr2013 Yr2012 Yr2011 Yr2010;
   array OutC3  $  C32014 C32013 C32012 C32011 C32010;
   array OutC4     C42014 C42013 C42012 C42011 C42010;
   set inter2;

   do _i = 1 to 5;
      OutYears(_i) = scan(InVars(_i), 1, '09'x);
      OutC3(_i) = scan(InVars(_i), 2, '09'x);
      OutC4(_i) = scan(InVars(_i), 3, '09'x);
   end;

   drop In2014-In2010 _i;
   output;
run;

[data_null__]

Did you know that you can have descending "Numbered Range Lists".

   array InVars $  In2014-In2010;
   array OutYears  Yr2014-Yr2010;
   array OutC3  $  C32014-C32010;
   array OutC4     C42014-C42010;

[art297]

If you download the free macro at A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity, here is a really easy line of code that will solve your problem:

%transpose(data=have, out=want, by=col1, delimiter=_, var=col2-col4)

[Ksharp]

Hi Arthur.T ,

Did you check the OP's output closely ? He asked a totally different layout :

A 2014 N 0    2013 X 1     2012 N 0      2011 X 2

B       .          2013 X 5     2012 X 0      2011 N 1      2010 N 0

They are all in wrong place with its original positon .Sorry, I am afraid that our macro wouldn't work for this scenario .

Xia Keshan

[art297]

: I missed the part about the variable ordering, but I still think it would work and be the easiest to understand.  How about:

data order;

  informat col2 $20.;

  format col2 $20.;

  input col2 order;

  cards;

2014   1

2013   2

2011   3

2010   4

;

%transpose(data=have, out=want, by=col1, id=col2, delimiter=_,

var=col2-col4, preloadfmt=order)

[Ksharp]

Yeah. But you need to make a ORDER dataset firstly right ?

BTW, your colleague Peter.E is now in Beijing,China .

Xia Keshan

[Ksharp]

Hi Arthur.T ,

Sorry, I think again, Yours might be more simple and easy to understand for OP .

and more important thing is OP don't need to understand how these SAS code work, Macro is good for him .

Regards

Xia Keshan

[art297]

: I made it even easier by adding a descendingid parameter to the macro.  Now the OP can obtain the file by running:

%transpose(data=have, out=want, by=col1, id=col2, delimiter=_, var=col2-col4, descendingid=yes)

The updated macro is available at the usual place, namely A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity. However, if you've already downloaded the macro from there, you'll probably have to refresh your browser screen after clicking on the link for the code and/or paper.

The preloadfmt parameter is still useful as it can deal with something like the following where you want direct control over the ordering of id values. In the case below I wanted them in the Tom, Dick, Harry order :

data have;

  informat name $5.;

  format name $5.;

  input year name height weight;

  cards;

2013 Dick 6.1 185

2013 Tom  5.8 163

2013 Harry 6.0 175

2014 Dick 6.1 180

2014 Tom  5.8 160

2014 Harry 6.0 195

;

data order;

  informat name $5.;

  format name $5.;

  input name order;

  cards;

Tom   1

Dick  2

Harry 3

;

%transpose(data=have, out=want, by=year, id=name,

delimiter=_, var=height weight, var_first=no, preloadfmt=order)