I did not see comparison to the group mean in the above response. Perhaps I overlooked it.
It would also be helpful to create a reproducible example so we can cut and paste your code in.
For clarity, I "stole" Richard's code to make comparison easier (athe random number seed is 123 , to keep it reproducible).
Also, might wish to add that the sascommunity.com website might be a better fit for this question, as you will likely get a set of answers from a wider set of perspectives.
That said, the approach is to divide your work into three parts
compute the age bucket and average salary and count for each age bucket
merge this to your main data set, and compute over/under for each person
Then, aggregate by combinations agebucket * sal_under_over
you can do this in a single pass of the data if you use SAS's hash table object to accumulate as you go. But that would take me too far afield, and I sense you'd prefer to avoid DATA step code for now.
Finally, a comment about usage - I certainly hope you are considering how this data will be used by management, and that you are not facilitating age discrimination in the workplace.
* group the ages. this is the more SAS idiomatic way to approach
* case statements, and this can be reused over several SAS PROC steps.;
proc format ;
value agebucket low-<50 = 'Under 50'
50-high = 'Over 50';
run;
** Richard code for a reproducible data set (thanks!);
data have;
do empid = 1 to 1000;
birth_date = intnx('YEAR', '01JAN15'd, -18 - rand('unif', 51)) - rand('unif',365);
if ranuni(123) < 0.15 then
term_date = intnx("year", birth_date, 18 + 30 * ranuni(123));
else
term_date = .;
salary = round(1000 * (30 + rand('unif', 75)),25);
output;
end;
format birth_date term_date yymmdd10. salary dollar11.2;
run;
** main work begins here;
* ages will be as of report date;
%let report_date="01Jan2019"d;
proc sql;
/* compute the category means */
create table category_means as
select put(intck('month',birth_date,&report_date.)/12,agebucket.) as agebucket,
mean(salary) as mean_salary,count(salary) as count_nmiss_salary
from have
group by 1;
/* join the category means to the main data */
create table analysis as
select t1.*,
intck('month',birth_date,&report_date.)/12 format=5.1 as age,
put(intck('month',birth_date,&report_date.)/12,agebucket.) as agebucket,
t2.mean_salary,count_nmiss_salary,
case when salary>mean_salary then 'Over' else 'Under' end as sal_und_over
from have t1 inner join category_means t2
on put(intck('month',birth_date,&report_date.),agebucket.)=t2.agebucket;
/* reporting section */
title 'sample of data for analysis';
/* print first 10 rows for illustrative purposes */
reset inobs=10;
select * from analysis;
/* reporting section */
title 'Mean salaries for people who are already over mean salary by age category';
select agebucket, mean(salary) as mean,count(salary) as count
from analysis
where sal_und_over='Over'
group by agebucket;
run;
Also, there are ways to reduce the redundancy of the age and age bucket calculation. If you want to see them, so state in the comments. However, this duplication should not adversely affect your performance by too much.
If you are going to be using SAS I urge you to learn the SAS idiomatic way of doing this. PROC MEANS is pretty much the lingua franca for this type of computation.
