topic Re: analysing observations within an observation group, pref. proc sql in SAS Programming

analysing observations within an observation group, pref. proc sql

metallon — Thu, 21 Apr 2016 13:22:49 GMT

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.

Re: analysing observations within an observation group, pref. proc sql

FreelanceReinh — Thu, 21 Apr 2016 13:38:36 GMT

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.

Re: analysing observations within an observation group, pref. proc sql

RW9 — Thu, 21 Apr 2016 14:04:41 GMT

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;

Re: analysing observations within an observation group, pref. proc sql

Reeza — Thu, 21 Apr 2016 17:17:18 GMT

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

Re: analysing observations within an observation group, pref. proc sql

PGStats — Thu, 21 Apr 2016 17:52:48 GMT

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;

Re: analysing observations within an observation group, pref. proc sql

FreelanceReinh — Thu, 21 Apr 2016 18:33:20 GMT

@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.