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
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;
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;
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;
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
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;
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;
Thankyou Ksharp it worked for me like a charm.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.