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.
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;
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.
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;
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
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;
@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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.