proc transpose

[Ronein]

Hello

I have a data set called aaa and by using proc transpose I want to get another data set called bbb.

The result of bbb data set is not as I want because under column "pet" I expect to have only 3 rows: dog,cat,fish

Data aaa;
input pet_owner $ pet $  poulation;
cards;
Dave dog 2
Dave cat 1 
Yulia dog 2
Yulia cat 2
Joe fish 7
Joe dog 1
Joe cat 1
;
run;


proc transpose data=aaa name=Metric out=bbb;
id pet_owner;
by pet NOTSORTED;
var poulation;
run;

[andreas_lds · Accepted solution]

The result you get is 100% correct when using "notsorted", the docs say: "If observations with the same values for the BY variables are not contiguous, then the procedure treats each contiguous set as a separate BY group."

You will have to sort dataset aaa to get what you want.

[Astounding]

Sort the data by PET, and remove the word NOTSORTED.