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
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.
For testing purposes name each dataset differently (eg. temp1, temp2, final), then you will see the difference with and without nodupkey.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: