analysing observations within an observation group, pref. proc sql

[metallon]

Hi SAS Experts,

I cant get my head around it. I need to
mark the older animal within an animal-group with a higher Size as 'x' but in case the
younger animal has a higher Size value with a 'y'

Animal Age Size
1    13    18
1     7      20
2     6      17
2     2      18
3     5      17
3     3    25

Any ideas? Preferable using proc sql.

I think in Oracle this would work with partitioning the data set.

[FreelanceReinh]

Hi @metallon,

This should be a start:

data have;
input Animal Age Size;
cards;
1 13 18
1 7 20
2 6 17
2 2 18
3 5 17
3 3 15
;

proc sql;
select a.*, case when a.size>b.size>. then 'x'
                 when a.size<b.size   then 'y'
            end as mark
from have a left join have b
on a.animal=b.animal & a.age>b.age;
quit;

Please note that I've modified the last Size value to let one 'x' appear.

[RW9 · Accepted solution]

Its a basic sort and by group?  Not sure what the complication is on this  - you could include it in any other computational block also.  Other options include merging/joining.

proc sort data=have;
  by animal age size;
run;
data have;
  set have;
  by animal;
  if first.animal then size_result="y";
  else size_result="x";
run;

[Reeza]

Partitions are not supported in SAS SQL - the equivalent is usually using a BY group within a data set. 

BY group processing is incredibly powerful and worth learning.

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a001283274.htm

[PGStats]

This is how I translate your requirements into a SAS/SQL query (I added a case where the older animal was also the largest) :

data animals;
input AnimalGroup Age Size;
datalines;
1    13    18
1     7      20
2     6      17
2     2      18
3     5      17
3     3    25
4     10   10
4     8    8
;

proc sql;
select 
    *,
    case 
        when age = max(age) and size=max(size) then "x"
        when size = max(size) then "y"
        else " "
        end as maxCode
from animals
group by animalGroup
;
quit;

[FreelanceReinh]

@metallon: Now you've got three solutions producing three different results. A good example of why it makes sense to provide not only sample data, but also sample output.