Need help combining and sorting data into one row

[arde]

Hi,

I have a bunch of data that I would like to combine into one row and sort at the same time:

have:

ID	Rating	Color
Flower-1	6	Purple
Flower-1	5	Green
Flower-2	1	Red
Flower-2	4	Indigo
Plant-1	10	Orange
Plant-1	2	Yellow
Plant-2	4	Blue
Plant-2	4	Violet
Plant-3	8	Black
Plant-3	6	White

I would like to combine similar IDs while sorting based on Rating:

want:

ID	Rating1	Color1	Rating2	Color2
Flower-1	5	Green	6	Purple
Flower-2	1	Red	4	Indigo
Plant-1	2	Yellow	10	Orange
Plant-2	4	Blue	4	Violet
Plant-3	6	White	8	Black

Any help is truly appreciated.  Thank you!

[ballardw]

Is rating numeric or character?

Sort by what (I hate guessing from examples as other data may affect the order)

Why do you want a data set like that? What will you do with it? For most purposes the current data is more useful.

[arde]

The rating is numeric.
Sorting is to be done by the rating.
I need the dataset to look like that because I have another dataset that I need to merge IDs.

[Tom]

I do not understand this sentence.

I need the dataset to look like that because I have another dataset that I need to merge IDs.

Do you mean you have another dataset that also has the ID variable that you want MERGE with the current one? 

Why would it help the merge  to change the current dataset to have one observation per ID ?  Does the other dataset also have multiple observation per value of ID?

[Ksharp · Accepted solution]

data have;
infile cards expandtabs truncover;
input ID $ Rating	Color $;
cards;
Flower-1	6	Purple
Flower-1	5	Green
Flower-2	1	Red
Flower-2	4	Indigo
Plant-1	10	Orange
Plant-1	2	Yellow
Plant-2	4	Blue
Plant-2	4	Violet
Plant-3	8	Black
Plant-3	6	White
;
proc sort data=have out=temp;
by id rating;
run;
data temp;
 set temp;
 by id;
 if first.id then n=0;
 n+1;
run;
proc transpose data=temp out=temp2;
by id n;
var rating color;
run;
proc transpose data=temp2 out=want(drop=_NAME_);
by id ;
var col1;
id _name_ n;
run;

[Ksharp]

And the most safe and easy way is using PROC SUMMARY.

data have;
infile cards expandtabs truncover;
input ID $ Rating	Color $;
cards;
Flower-1	6	Purple
Flower-1	5	Green
Flower-2	1	Red
Flower-2	4	Indigo
Plant-1	10	Orange
Plant-1	2	Yellow
Plant-2	4	Blue
Plant-2	4	Violet
Plant-3	8	Black
Plant-3	6	White
;
proc sort data=have out=temp;
by id rating;
run;
proc sql noprint;
select max(n) into :n from
 (select count(*) as n from temp group by id);
quit;
proc summary data=temp ;
by id;
output out=want(drop=_type_ _freq_) idgroup(out[&n] (rating color)=);
run;

[whymath]

If you are going to report this data, you dont't really need to combining manually. Based on dataset of @Ksharp , you can use across usage in proc report:

proc sort data=have;
  by id rating;
run;

data want;
  set have;
  by id rating;

  aseq+1;
  if first.id then aseq=1;
run;

proc report data=want;
  column id aseq,(rating color n);
  define id     /group;
  define aseq   /across;
  define rating /display;
  define color  /display;
  define n      /noprint;
run;