BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ervinodsingh
Obsidian | Level 7

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

6 REPLIES 6
Tom
Super User Tom
Super User

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

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;

 

ervinodsingh
Obsidian | Level 7

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

Ksharp
Super User

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

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;
ervinodsingh
Obsidian | Level 7

Thankyou Ksharp it worked for me like a charm.

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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