Re: What is the smallest and largest individual for Charge Hour for Jo...

ptnawoda · Posted 05-22-2020 12:38 PM

Hello, everyone, I'm seeking your kind help to solve the below-mentioned questions using PROC SQL.
I tried many ways but didn't get correct answers.
Here is the queries for create and insert statements.

PROC SQL;
create table Aschema.JOB2019(
JOB_CODE2019 num,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 num(4,2),
constraint pkjob1 primary key (JOB_CODE2019)
);
insert into Aschema.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 Aschema.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 Aschema.JOB2019(JOB_CODE2019)
);
insert into Aschema.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 Aschema.PROJECT2019(
PROJ_NUM2019 num,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 num,
constraint pkproject1 primary key (PROJ_NUM2019),
constraint pkforiegn1 foreign key (EMP_NUM2019) references Aschema.employee2019(EMP_NUM2019)
);
insert INTO Aschema.project2019
values (15,'Evergreen',105)
values (18,'Amber Wave',104)
values (22,'Rolling Tide',113)
values (25,'Starflight',101); 
QUIT;

PROC SQL;
create table Aschema.ASSIGNMENT2019(
ASSIGN_NUM2019 num,
ASSIGN_DATE2019 date,
PROJ_NUM2019 num,
EMP_NUM2019 num,
ASSIGN_HOURS2019 num(5,2),
ASSIGN_CHG_HOUR2019 num(5,2),
ASSIGN_CHARGE2019 num(7,2) ,
constraint pkassignment1 primary key (ASSIGN_NUM2019),
constraint fkassignment1 foreign key (PROJ_NUM2019) references Aschema.project2019(PROJ_NUM2019),
constraint fkassignment2 foreign key (EMP_NUM2019) references Aschema.employee2019(EMP_NUM2019)
);                                                                                    
insert into Aschema.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;
Update Aschema.assignment2019
set ASSIGN_CHARGE2019 = ASSIGN_HOURS2019 * ASSIGN_CHG_HOUR2019;
QUIT;

These are the questions,

1.Show which projects bill hours between 45 and 100 hours

2.What is the smallest and largest individual for Charge Hour for Job Classes?

3.Which Job Class is the second highest Charge Hour in the table?

4.Which Project does not utilize an Application Designer?

5.Identify the employees who are Database Designer and which project have they been associated with?

I'm beginner to PROC SQL and your help with these questions will be highly appreciated.

Thank you.

ballardw · Posted 05-22-2020 01:05 PM

Are you required to do this with SQL?

Several of these are easier with other SAS procs like Proc Summary.

ptnawoda · Posted 05-22-2020 01:16 PM

Hello Sir,
I'm completely new for SAS so I started doing these with PROC SQL.

ptnawoda · Posted 05-23-2020 02:36 PM

Hello everyone,
I need to answer this tricky question in PROC SQL,
I tried many ways and finally got this query which retrieves only MAX SUM or MIN SUM without the respective employee.

PROC SQL;
select MAX(t.SUM_CHARGE)
from
(select DISTINCT e.JOB_CODE1482784,a.EMP_NUM1482784,a.PROJ_NUM1482784,SUM(a.ASSIGN_CHG_HOUR1482784) as SUM_CHARGE
	 from _784.ASSIGNMENT1482784 a inner join _784.EMPLOYEE1482784 e 
	 on a.EMP_NUM1482784 = e.EMP_NUM1482784
	 inner join _784.job1482784 j
	 on  e.JOB_CODE1482784 = j.JOB_CODE1482784
	 group by a.EMP_NUM1482784)t
group by t.JOB_CODE1482784 ;
Quit;

here is the table create and insert statement.

PROC SQL;
create table Aschema.JOB2019(
JOB_CODE2019 num,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 num(4,2),
constraint pkjob1 primary key (JOB_CODE2019)
);
insert into Aschema.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 Aschema.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 Aschema.JOB2019(JOB_CODE2019)
);
insert into Aschema.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 Aschema.PROJECT2019(
PROJ_NUM2019 num,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 num,
constraint pkproject1 primary key (PROJ_NUM2019),
constraint pkforiegn1 foreign key (EMP_NUM2019) references Aschema.employee2019(EMP_NUM2019)
);
insert INTO Aschema.project2019
values (15,'Evergreen',105)
values (18,'Amber Wave',104)
values (22,'Rolling Tide',113)
values (25,'Starflight',101); 
QUIT;

PROC SQL;
create table Aschema.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 Aschema.project2019(PROJ_NUM2019),
constraint fkassignment2 foreign key (EMP_NUM2019) references Aschema.employee2019(EMP_NUM2019)
);                                                                                    
insert into Aschema.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;

I highly appreciate your help to solve the above question.

Thank you.

PGStats · Posted 05-27-2020 06:52 PM

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.

PG

ptnawoda · Posted 05-23-2020 02:34 PM

Hello everyone,
I need to answer this tricky question in PROC SQL,
I tried many ways and finally got this query which retrieves only MAX SUM or MIN SUM without the respective employee.

PROC SQL;
select MAX(t.SUM_CHARGE)
from
(select DISTINCT e.JOB_CODE1482784,a.EMP_NUM1482784,a.PROJ_NUM1482784,SUM(a.ASSIGN_CHG_HOUR1482784) as SUM_CHARGE
	 from _784.ASSIGNMENT1482784 a inner join _784.EMPLOYEE1482784 e 
	 on a.EMP_NUM1482784 = e.EMP_NUM1482784
	 inner join _784.job1482784 j
	 on  e.JOB_CODE1482784 = j.JOB_CODE1482784
	 group by a.EMP_NUM1482784)t
group by t.JOB_CODE1482784 ;
Quit;

here is the table create and insert statement.

PROC SQL;
create table Aschema.JOB2019(
JOB_CODE2019 num,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 num(4,2),
constraint pkjob1 primary key (JOB_CODE2019)
);
insert into Aschema.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 Aschema.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 Aschema.JOB2019(JOB_CODE2019)
);
insert into Aschema.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 Aschema.PROJECT2019(
PROJ_NUM2019 num,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 num,
constraint pkproject1 primary key (PROJ_NUM2019),
constraint pkforiegn1 foreign key (EMP_NUM2019) references Aschema.employee2019(EMP_NUM2019)
);
insert INTO Aschema.project2019
values (15,'Evergreen',105)
values (18,'Amber Wave',104)
values (22,'Rolling Tide',113)
values (25,'Starflight',101); 
QUIT;

PROC SQL;
create table Aschema.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 Aschema.project2019(PROJ_NUM2019),
constraint fkassignment2 foreign key (EMP_NUM2019) references Aschema.employee2019(EMP_NUM2019)
);                                                                                    
insert into Aschema.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;

I highly appreciate your help to solve above question.

Thank you.

SASKiwi · Posted 05-25-2020 11:20 PM

@ptnawoda - It looks like you are trying to do all of this in an external database.

It would save us a whole lot of time if you could provide your sample data in a SAS DATA step as described here: Data in a DATA Step

What is the smallest and largest individual for Charge Hour for Job Classes?

Re: What is the smallest and largest individual for Charge Hour for Job Classes?

Re: What is the smallest and largest individual for Charge Hour for Job Classes?

What is the smallest and largest individual for Charge Hour for Job Class?

Re: What is the smallest and largest individual for Charge Hour for Job Class?

What is the smallest and largest individual for Charge Hour for Job Class?

Re: What is the smallest and largest individual for Charge Hour for Job Class?