Contributor
Posts: 23

# Find min and max of the same column in sql

I need to print a table usin sql to show youngest and oldest of each customer_type.

The desired table is

oldest = the Customer_ID value of the oldest customer
oldest_bday = the Customer_Birthdate of the oldest customer
youngest = the Customer_ID value of the youngest customer
youngest_bday = the Customer_Birthdate of the youngest customer
agerange = the number of years (rounded to the nearest tenth) between the age of
the oldest and youngest members of that type; use Customer_Birthdate instead of theCustomer_Age variable to calculate agerange

I tried four sql, can anyone tell how to write this and let it be more efficient?

Super User
Posts: 19,822

## Re: Find min and max of the same column in sql

What does your code look like so far?

Please post data as text minimum and ideally as a data step. No one really wants to type out both your data and code for an answer.

ydu180 wrote:

I need to print a table usin sql to show youngest and oldest of each customer_type.

The desired table is

oldest = the Customer_ID value of the oldest customer
oldest_bday = the Customer_Birthdate of the oldest customer
youngest = the Customer_ID value of the youngest customer
youngest_bday = the Customer_Birthdate of the youngest customer
agerange = the number of years (rounded to the nearest tenth) between the age of
the oldest and youngest members of that type; use Customer_Birthdate instead of theCustomer_Age variable to calculate agerange

I tried four sql, can anyone tell how to write this and let it be more efficient?

Contributor
Posts: 23

## Re: Find min and max of the same column in sql

``````proc sort data=customer_club out=customers;
by Customer_Type Customer_BirthDate;
run;

data clubextremes;
set customers;
by Customer_Type;

retain oldest oldest_bday youngest youngest_bday;
if first.Customer_Type=1 then do;
oldest=Customer_ID;
oldest_bday=Customer_BirthDate;
end;
if last.Customer_Type=1 then do;
youngest=Customer_ID;
youngest_bday=Customer_BirthDate;
agerange=(youngest_bday-oldest_bday)/365.25;
output;
end;
keep Customer_Type oldest youngest oldest_bday youngest_bday agerange;
run;``````

This is data stpes, but I do not know how to write it in sql.

Super User
Posts: 19,822

## Re: Find min and max of the same column in sql

Your previous answer said you 'tried four sql'. What does that look like? I get that it's not correct but I'd like to see what you've tried so far.

Here's a basic tutorial that may get you started.

https://stats.idre.ucla.edu/sas/modules/collapsing-across-observations-using-proc-sql/

Super Contributor
Posts: 440

## Re: Find min and max of the same column in sql

You haven't given us a sample of your disaggregate data so I can only give you an example with another data set. Here's how you would find the min and max of the age column by sex in SASHELP.CLASS

``````proc sort data=sashelp.class out=class;
by sex;
run;

Proc sql;
select sex, max(age) as oldest, min(age) as youngest
from class
group by sex;
quit;``````

Discussion stats
• 4 replies
• 231 views
• 0 likes
• 3 in conversation