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?
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?
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.
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/
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.