topic Re: Find min and max of the same column in sql in SAS Studio

Find min and max of the same column in sql

ydu180 — Tue, 01 Aug 2017 17:09:16 GMT

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?

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

Reeza — Tue, 01 Aug 2017 17:11:56 GMT

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?

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

ydu180 — Tue, 01 Aug 2017 17:37:16 GMT

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.

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

Reeza — Tue, 01 Aug 2017 17:41:20 GMT

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/

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

ChrisBrooks — Fri, 04 Aug 2017 00:56:19 GMT

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;