topic Find min and max salary for each dept_id using proc sql and datastep in SAS Programming

Find min and max salary for each dept_id using proc sql and datastep

BrahmanandaRao — Wed, 31 Jan 2024 11:29:09 GMT

data ds;
length emp_name $20;
input emp_name $ dep_id salary;
datalines;
Siva 1 30000
Ravi 2 40000
Prasad 1 50000
Arun 1  30000
Sai 2 20000
;
run;

proc sql;
create table _temp as 
select dep_id,max(salary) as max_sal ,min(salary) as min_sal

from ds
group by dep_id ; 

create table _temp as 
select a.* ,b.emp_name as emp_name_min,c.emp_name as emp_name_max
from _temp as a
left join ds as b
on a.min_sal=b.salary
left join  ds as  c
on a.max_sal=c.salary;
quit;

proc sort data=_temp nodupkey ;
by dep_id;
run;
proc print;
run;

How to get min and max salary for each dept_id using proc sql and datastep in the above code result I got only one minsalary from dep_id 1 but given data two min salary are same how to fix this

Re: Find min and max salary for each dept_id using proc sql and datastep

Quentin — Wed, 31 Jan 2024 11:35:03 GMT

The sort step with NODUPKEY is removing duplicates. If you remove the NODUPKEY option, you will have two records for department 1 in your output.

Re: Find min and max salary for each dept_id using proc sql and datastep

BrahmanandaRao — Wed, 31 Jan 2024 11:38:47 GMT

Nope even if not used nodupkey same result

Re: Find min and max salary for each dept_id using proc sql and datastep

Quentin — Wed, 31 Jan 2024 11:42:06 GMT

Did you rerun the full code, starting with the step that creates the data?

Re: Find min and max salary for each dept_id using proc sql and datastep

A_Kh — Wed, 31 Jan 2024 17:04:35 GMT

For testing purposes name each dataset differently (eg. temp1, temp2, final), then you will see the difference with and without nodupkey.

Re: Find min and max salary for each dept_id using proc sql and datastep

Tom — Wed, 31 Jan 2024 19:32:44 GMT

Please explain in words what you are trying to do. And explain what you think the steps are doing.

Especially that last step that is trying to "join" by matching SALARY to MAX_SAL.

NOTE that PROC SQL will happily re-merge the aggregate statistics for you automatically.

data ds;
  input emp_name :$20. dep_id salary;
datalines;
Siva 1 30000
Ravi 2 40000
Prasad 1 50000
Arun 1  30000
Sai 2 20000
;

proc sql;
create table ds_sal_range as
  select *
    , max(salary) as dept_max_salary
    , min(salary) as dept_min_salary 
  from ds
  group by dep_id
  order by emp_name
;
quit;

Result

                                       dept_max_    dept_min_
Obs    emp_name    dep_id    salary      salary       salary

 1      Arun          1       30000      50000        30000
 2      Prasad        1       50000      50000        30000
 3      Ravi          2       40000      40000        20000
 4      Sai           2       20000      40000        20000
 5      Siva          1       30000      50000        30000