Write and run SAS programs in your web browser

Find min and max of the same column in sql

Reply
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 微信图片_20170802010653.png

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: 17,818

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 微信图片_20170802010653.png

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: 17,818

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/

Regular Contributor
Posts: 182

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;

 

Ask a Question
Discussion stats
  • 4 replies
  • 203 views
  • 0 likes
  • 3 in conversation