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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.