Hi, I am new to SAS and hope for your help. I am currently using a query which yields a table similar to this: Attribute1 Attribute2 Attribute3 Attribute4 Count A x k num1 1 A y k num4 1 A z m num4 1 B x k num2 2 B z k num9 2 B a m num4 2 B b m num7 2 C y k num2 2 ... The current query is approx. like this (I am not allowed to use original data, therefore the strange naming...): proc sql; create table .... as select Attr1, Attr2, Attr3, Attr4, count(*) from Sourcetable where (Attr4 between ... and ... or Attr 4 between ... and ...) and Attr1 in (...) group by Attr1, Attr2, Attr3, Attr4 order by Attr1, Attr2, Attr3, Attr4; quit; The Sourcetable also contains, amongst others, Attribute 5. I would like to include Attribute 5, which is not necessarily a number, after Attribute 4 or after count(*). I do not want to group by Attribute 5 itself. In the grouping I want to see the value of Attribute 5 for which Attribute 4 (which is a number) is highest (and Attribute 1, Attribute 2, Attribute 3 are identical withing the grouping). I tried to adapt the above query using row_number and partition by, but I learned that this is not possible in SAS: I make up a specific example: Let Sourcetable be as follows: COLOR HOUSE TYPE DAY NAME NOMATTER1 NOMATTER2 2 3 garage 70 mickey blabla k 2 3 garage 71 minni ozk k 2 7 bungalow 15 nice -- l 2 7 bungalow 16 bad 1,1 l 2 8 bungalow 3 cold x l 5 2 garage 7 10 NULL r 5 80 bungalow 8 15 Hi k 5 80 garage 9 8 dog s 5 100 school 1 2 tree k ... I then want to modify my above proc sql query in a way to obtain from it the following set of rows: COLOR HOUSE TYPE DAY NAME COUNT 2 3 garage 71 minni 2 2 7 bungalow 16 bad 2 2 8 bungalow 3 cold 1 5 2 garage 7 10 1 5 80 bungalow 8 15 1 5 80 garage 9 8 1 ... Sorry for being complicated or even unclear, only just beginning. Thanks a lot for any help.
... View more
