BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
FreelanceReinh
Jade | Level 19

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Opal | Level 21

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
FreelanceReinh
Jade | Level 19

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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