This version of the code runs for me:
PROC SQL;
create table WORK.JOB2019(
JOB_CODE2019 num,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 num(4,2),
constraint pkjob1 primary key (JOB_CODE2019)
);
insert into WORK.job2019
values (500,'Programmer',35.75)
values (501,'Systems Analyst',96.75)
values (502,'Database Designer',105.00)
values (503,'Electrical Engineer',84.50)
values (504,'Mechanical Engineer',67.90)
values (505,'Civil Engineer',55.78)
values (506,'Clerical Support',26.87)
values (507,'DSS Analyst',45.95)
values (508,'Applications Designer',48.10)
values (509,'Bio Technician',34.55)
values (510,'General Support',18.36);
QUIT;
PROC SQL;
create table WORK.EMPLOYEE2019(
EMP_NUM2019 num,
EMP_LNAME2019 varchar(20),
EMP_FNAME2019 varchar(20),
EMP_INITIAL2019 char(1),
EMP_HIREDATE2019 date,
JOB_CODE2019 int,
constraint pkemployee1 primary key (EMP_NUM2019),
constraint fkemployee1 foreign key (JOB_CODE2019) references WORK.JOB2019(JOB_CODE2019)
);
insert into WORK.employee2019
values (101,'News','John','G','08Nov2000'd,502)
values (102,'Senior','David','H','12Jun1989'd,501)
values (103,'Arbough','June','E','01Dec1997'd,503)
values (104,'Ramoras','Anne','K','15Nov1988'd,501)
values (105,'Johnson','Alice','K','01Feb1994'd,502)
values (106,'Smithfield','William',' ','22Jun2005'd,500)
values (107,'Alonzo','Maria','D','10Oct1994'd,500)
values (108,'Washington','Ralph','B','22Aug1889'd,501)
values (109,'Smith','Larry','W','18Jul1999'd,501)
values (110,'Olenko','Gerald','A','11Dec1996'd,505)
values (111,'Wabash','Geoff','B','04Apr1989'd,506)
values (112,'Smithson','Darlene','M','23Oct1995'd,507)
values (113,'Joenbrood','Delbert','K','15Nov1994'd,508)
values (114,'Jones','Annelise',' ','20Aug1991'd,508)
values (115,'Bawangi','Travis','B','25Jan1990'd,501)
values (116,'Pratt','Gerald','L','05Mar1995'd,510)
values (117,'Williamson','Angie','H','19Jun1994'd,509)
values (118,'Frommer','James','J','04Jan2006'd,510);
QUIT;
PROC SQL;
create table WORK.PROJECT2019(
PROJ_NUM2019 num,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 num,
constraint pkproject1 primary key (PROJ_NUM2019),
constraint pkforiegn1 foreign key (EMP_NUM2019) references WORK.employee2019(EMP_NUM2019)
);
insert INTO WORK.project2019
values (15,'Evergreen',105)
values (18,'Amber Wave',104)
values (22,'Rolling Tide',113)
values (25,'Starflight',101);
QUIT;
PROC SQL;
create table WORK.ASSIGNMENT2019(
ASSIGN_NUM2019 num,
ASSIGN_DATE2019 date,
PROJ_NUM2019 num,
EMP_NUM2019 num,
ASSIGN_HOURS2019 num(5,2),
ASSIGN_CHG_HOUR2019 num(5,2),
constraint pkassignment1 primary key (ASSIGN_NUM2019),
constraint fkassignment1 foreign key (PROJ_NUM2019) references WORK.project2019(PROJ_NUM2019),
constraint fkassignment2 foreign key (EMP_NUM2019) references WORK.employee2019(EMP_NUM2019)
);
insert into WORK.assignment2019(ASSIGN_NUM2019,ASSIGN_DATE2019,PROJ_NUM2019,EMP_NUM2019,ASSIGN_HOURS2019,ASSIGN_CHG_HOUR2019)
values(1001,'04Mar2012'd,15,103,2.6,84.50)
values (1002,'04Mar2012'd,18,118,1.4,18.36)
values (1003,'05Mar2012'd,15,101,3.6,105.00)
values (1004,'05Mar2012'd,22,113,2.5,48.10)
values (1005,'05Mar2012'd,15,103,1.9,84.50)
values (1006,'05Mar2012'd,25,115,4.2,96.75)
values (1007,'05Mar2012'd,22,105,5.2,105.00)
values (1008,'05Mar2012'd,25,101,1.7,105.00)
values (1009,'05Mar2012'd,15,105,2.0,105.00)
values (1010,'06Mar2012'd,15,102,3.8,96.75)
values (1011,'06Mar2012'd,22,104,2.6,96.75)
values (1012,'06Mar2012'd,15,101,2.3,105.00)
values (1013,'06Mar2012'd,25,114,1.8,48.10)
values (1014,'06Mar2012'd,22,111,4.0,26.87)
values (1015,'06Mar2012'd,25,114,3.4,48.10)
values (1016,'06Mar2012'd,18,112,1.2,45.95)
values (1017,'06Mar2012'd,18,118,2.0,18.36)
values (1018,'06Mar2012'd,18,104,2.6,96.75)
values (1019,'06Mar2012'd,15,103,3.0,84.50)
values (1020,'07Mar2012'd,22,105,2.7,105.00)
values (1021,'08Mar2012'd,25,108,4.2,96.75)
values (1022,'07Mar2012'd,25,114,5.8,48.10)
values (1023,'07Mar2012'd,22,106,2.4,35.75);
QUIT;
PROC SQL;
select
JOB_CODE2019,
case when SUM_CHARGE = max(SUM_CHARGE) then "Max" else "Min" end as MinMax,
EMP_NUM2019,
SUM_CHARGE
from
(select
e.JOB_CODE2019,
a.EMP_NUM2019,
SUM(a.ASSIGN_CHG_HOUR2019) as SUM_CHARGE
from WORK.ASSIGNMENT2019 a inner join
WORK.EMPLOYEE2019 e on a.EMP_NUM2019 = e.EMP_NUM2019 inner join
WORK.job2019 j on e.JOB_CODE2019 = j.JOB_CODE2019
group by e.JOB_CODE2019, a.EMP_NUM2019)
group by JOB_CODE2019
having SUM_CHARGE = max(SUM_CHARGE) or SUM_CHARGE = min(SUM_CHARGE)
order by JOB_CODE2019, SUM_CHARGE;
Quit;
JOB_CODE2019 MinMax EMP_NUM2019 SUM_CHARGE
500 Max 106 35.75
501 Min 102 96.75
501 Min 108 96.75
501 Min 115 96.75
501 Max 104 193.5
502 Max 105 315
502 Max 101 315
503 Max 103 253.5
506 Max 111 26.87
507 Max 112 45.95
508 Min 113 48.1
508 Max 114 144.3
510 Max 118 36.72
I think the most crucial part that you were missing is the HAVING clause. Also note that the query uses the automatic remerging supported by SAS/SQL but not all other DBMS.
... View more