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.
Are you required to do this with SQL?
Several of these are easier with other SAS procs like Proc Summary.
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.
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.
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.
@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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.