Update Rows based on Condition

[DavidPhillips2]

Is there an efficient way to modify table retention to table retention 2.  I can think of a few ways that might be hard for others to follow when looking at the code later on.

The business role is that students are enrolled up to the point that they are not retained.  If the student transfers out within one semester of transferring out then first semester of not retained is altered to transferred out.  Column semester count is included to update transfer outs for three semesters.  The logic is to update person_id records where semester count is in 1,2, 3 and any of the semesters is transfer and of none of those records are enrolled.

/*create the table*/
proc sql;
CREATE TABLE Retention (
Person_ID int,
Academic_period varchar(6),
Semester_Count int,
Attend_Status varchar(50)
);

insert into retention (person_Id, academic_period, semester_count, attend_status) values (1, '200010', 0, 'Enrolled');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (1, '200020', 1, 'Not Retained');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (1, '200030', 2, 'Transfer');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (1, '200110', 3, 'Transfer');

insert into retention (person_Id, academic_period, semester_count, attend_status) values (2, '200010', 0, 'Enrolled');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (2, '200020', 1, 'Not Retained');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (2, '200030', 2, 'Transfer');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (2, '200110', 3, 'Enrolled');

proc sql;
CREATE TABLE Retention2 (
Person_ID int,
Academic_period varchar(6),
Semester_Count int,
Attend_Status varchar(50)
);

insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (1, '200010', 0, 'Enrolled');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (1, '200020', 1, 'Transfer');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (1, '200030', 2, 'Transfer');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (1, '200110', 3, 'Transfer');

insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (2, '200010', 0, 'Enrolled');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (2, '200020', 1, 'Not Retained');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (2, '200030', 2, 'Transfer');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (2, '200110', 3, 'Enrolled');

[DavidPhillips2 · Accepted solution]

This worked:

/*create the table*/
proc sql;
CREATE TABLE Retention (
Person_ID int,
Academic_period varchar(6),
Semester_Count int,
Attend_Status varchar(50)
);

insert into retention (person_Id, academic_period, semester_count, attend_status) values (1, '200010', 0, 'Enrolled');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (1, '200020', 1, 'Not Retained');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (1, '200030', 2, 'Transfer');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (1, '200110', 3, 'Transfer');

insert into retention (person_Id, academic_period, semester_count, attend_status) values (2, '200010', 0, 'Enrolled');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (2, '200020', 1, 'Not Retained');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (2, '200030', 2, 'Transfer');
insert into retention (person_Id, academic_period, semester_count, attend_status) values (2, '200110', 3, 'Enrolled');

CREATE TABLE Retention2 (
Person_ID int,
Academic_period varchar(6),
Semester_Count int,
Attend_Status varchar(50)
);

insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (1, '200010', 0, 'Enrolled');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (1, '200020', 1, 'Transfer');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (1, '200030', 2, 'Transfer');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (1, '200110', 3, 'Transfer');

insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (2, '200010', 0, 'Enrolled');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (2, '200020', 1, 'Not Retained');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (2, '200030', 2, 'Transfer');
insert into retention2 (person_Id, academic_period, semester_count, attend_status) values (2, '200110', 3, 'Enrolled');

create table TransferOutUpdateList as
select distinct person_id
from retention
where semester_count in (1,2,3) and Attend_Status = 'Transfer';

update retention set Attend_Status = 'Transfer'
where person_id in (select distinct person_id from TransferOutUpdateList) and attend_status = 'Not Retained' and semester_count in (1,2,3);

quit;