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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.