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

## Need help combining and sorting data into one row

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Need help combining and sorting data into one row

``````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;``````
6 REPLIES 6
Super User

## Re: Need help combining and sorting data into one row

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.

Obsidian | Level 7

## Re: Need help combining and sorting data into one row

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

## Re: Need help combining and sorting data into one row

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?

Super User

## Re: Need help combining and sorting data into one row

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

## Re: Need help combining and sorting data into one row

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;``````
Lapis Lazuli | Level 10

## Re: Need help combining and sorting data into one row

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;``````
Discussion stats
• 6 replies
• 455 views
• 2 likes
• 5 in conversation