DATA Step, Macro, Functions and more

How to reshape data?

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

How to reshape data?

Hi have data like 

a 4

a 3

a 6

b 3

b 7

b 8

c 1

c 5

c 7

 

and want my data to the form 

4 3 1

3 7 5

6 8 7

 

 


Accepted Solutions
Solution
‎05-10-2017 03:04 AM
Super User
Posts: 10,035

Re: How to reshape data?

Posted in reply to ervinodsingh

Or the followng code could get you more slick.

No need input 3 by hand .

 

data have;
input id $ value;
cards;
a 4
a 3
a 6
b 3
b 7
b 8
c 1
c 5
c 7
;
run;
proc sql noprint;
select count(*) into : n from have group by id;
quit;

proc sort data=have;
 by id;
run;
proc iml;
use have;
read all var{id value};
close;
x=shapecol(value,&n);
vname=unique(id);
create want from x[c=vname];
append from x;
close;
quit;

View solution in original post


All Replies
Super User
Super User
Posts: 7,055

Re: How to reshape data?

Posted in reply to ervinodsingh

It really helps if you think of your data as variables and observations instead of matrices. Your input has two variables.

data have;
  input varname $ value ;
cards;
a 4
a 3
a 6
b 3
b 7
b 8
c 1
c 5
c 7
;

And your output data has three variables.

data want ;
  input a b c ;
cards;
4 3 1
3 7 5
6 8 7
;

What your input data is missing is anyway to tell explain why the observation should ahve A=4 and B=3 instead of say A=4 and B=7.

If we assume you want to keep the values in the same relative order as in the source data then you just need to add a variable to your input data that you can use to group the observations.  So let's make a variable called GROUP.

data middle;
  set have ;
  by varname ;
  if first.varname then group=0;
  group +1;
run;
proc sort;
  by group varname;
run;

Then we can make the desired output using PROC TRANSPOSE.

proc transpose data=middle out=want ;
  by group;
  id varname ;
  var value;
run;
Super User
Posts: 10,035

Re: How to reshape data?

Posted in reply to ervinodsingh

It is very easy for IML code.

 

 

data have;
input id $ value;
cards;
a 4
a 3
a 6
b 3
b 7
b 8
c 1
c 5
c 7
;
run;
proc iml;
use have;
read all var{value};
close;
x=shapecol(value,3);

create want from x;
append from x;
close;
quit;

proc print noobs;run;

 

Contributor
Posts: 26

Re: How to reshape data?

HI Ksharp your trip worked for but is there any way to name columns in want data as a, b, c. Please suggest me.  Thanks

Super User
Posts: 10,035

Re: How to reshape data?

Posted in reply to ervinodsingh

Sure. It is a piece of cake for IML.

 

data have;
input id $ value;
cards;
a 4
a 3
a 6
b 3
b 7
b 8
c 1
c 5
c 7
;
run;
proc sort data=have;
 by id;
run;
proc iml;
use have;
read all var{id value};
close;
x=shapecol(value,3);
vname=unique(id);
create want from x[c=vname];
append from x;
close;
quit;
Solution
‎05-10-2017 03:04 AM
Super User
Posts: 10,035

Re: How to reshape data?

Posted in reply to ervinodsingh

Or the followng code could get you more slick.

No need input 3 by hand .

 

data have;
input id $ value;
cards;
a 4
a 3
a 6
b 3
b 7
b 8
c 1
c 5
c 7
;
run;
proc sql noprint;
select count(*) into : n from have group by id;
quit;

proc sort data=have;
 by id;
run;
proc iml;
use have;
read all var{id value};
close;
x=shapecol(value,&n);
vname=unique(id);
create want from x[c=vname];
append from x;
close;
quit;
Contributor
Posts: 26

Re: How to reshape data?

Thankyou Ksharp it worked for me like a charm.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 252 views
  • 3 likes
  • 3 in conversation