analysing observations within an observation group, pref. proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

analysing observations within an observation group, pref. proc sql

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.


Accepted Solutions
Solution
‎04-29-2016 05:07 AM
Super User
Super User
Posts: 7,720

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

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,116

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

[ Edited ]

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.

Solution
‎04-29-2016 05:07 AM
Super User
Super User
Posts: 7,720

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

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;
Super User
Posts: 19,167

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

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

 

 

Respected Advisor
Posts: 4,820

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

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;
PG
Trusted Advisor
Posts: 1,116

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 391 views
  • 7 likes
  • 5 in conversation