topic Re: Need help combining and sorting data into one row in SAS Programming

Need help combining and sorting data into one row

arde — Thu, 13 Jun 2024 18:52:50 GMT

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!

Re: Need help combining and sorting data into one row

ballardw — Thu, 13 Jun 2024 19:13:40 GMT

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.

Re: Need help combining and sorting data into one row

arde — Thu, 13 Jun 2024 19:27:54 GMT

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.

Re: Need help combining and sorting data into one row

Ksharp — Fri, 14 Jun 2024 01:36:13 GMT

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;

Re: Need help combining and sorting data into one row

Ksharp — Fri, 14 Jun 2024 01:40:58 GMT

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;

Re: Need help combining and sorting data into one row

Tom — Fri, 14 Jun 2024 01:56:39 GMT

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?

Re: Need help combining and sorting data into one row

whymath — Fri, 14 Jun 2024 06:32:48 GMT

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;