BookmarkSubscribeRSS Feed
davide
Calcite | Level 5

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

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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
Super User

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
Calcite | Level 5

Thanks a lot

Davide

TomKari
Onyx | Level 15

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__
Jade | Level 19

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
Opal | Level 21

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
Super User

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
Opal | Level 21

: 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
Super User

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

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

Xia Keshan

Ksharp
Super User

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
Opal | Level 21

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

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 1496 views
  • 0 likes
  • 7 in conversation