Update Rows based on Condition

Accepted Solution Solved
Reply
Valued Guide
Posts: 620
Accepted Solution

Update Rows based on Condition

[ Edited ]

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');


Accepted Solutions
Solution
‎07-20-2018 11:08 AM
Valued Guide
Posts: 620

Re: Update Rows based on Condition

Posted in reply to DavidPhillips2

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;

View solution in original post


All Replies
Solution
‎07-20-2018 11:08 AM
Valued Guide
Posts: 620

Re: Update Rows based on Condition

Posted in reply to DavidPhillips2

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 68 views
  • 0 likes
  • 1 in conversation