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

[BrahmanandaRao]

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

[Quentin]

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.

[BrahmanandaRao]

Nope even if not used nodupkey same result

[Quentin]

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

[A_Kh]

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

[Tom]

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