topic Re: transpose info in SAS Data Management

transpose info

davide — Mon, 01 Sep 2014 12:34:20 GMT

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

Re: transpose info

RW9 — Mon, 01 Sep 2014 12:50:30 GMT

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;

Re: transpose info

Loko — Mon, 01 Sep 2014 14:30:30 GMT

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;

Re: transpose info

Ksharp — Mon, 01 Sep 2014 14:53:21 GMT

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

Re: transpose info

davide — Mon, 01 Sep 2014 16:11:17 GMT

Thanks a lot

Davide

Re: transpose info

TomKari — Wed, 03 Sep 2014 20:01:21 GMT

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;

Re: transpose info

data_null__ — Wed, 03 Sep 2014 21:36:16 GMT

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;

Re: transpose info

art297 — Thu, 04 Sep 2014 04:37:45 GMT

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)

Re: transpose info

Ksharp — Thu, 04 Sep 2014 12:40:11 GMT

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

Re: transpose info

art297 — Thu, 04 Sep 2014 13:00:25 GMT

: 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)

Re: transpose info

Ksharp — Thu, 04 Sep 2014 13:33:09 GMT

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

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

Xia Keshan

Re: transpose info

Ksharp — Thu, 04 Sep 2014 14:26:11 GMT

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

Re: transpose info

art297 — Thu, 04 Sep 2014 14:43:19 GMT

: 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)