topic Re: Fill in Blanks on Last Column when Value Found in SAS Procedures

Fill in Blanks on Last Column when Value Found

DavidPhillips2 — Mon, 16 Jul 2018 17:23:24 GMT

How can I convert table 1 to table 2? I’m trying to fill in the blanks on the last column only after a value is found.

proc sql;
create table test (
id int,
cohort varchar(100),
term varchar(100),
class varchar(1)
);

insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2016', '');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2015', 'S');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2014', 'S');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2013', '');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2012', 'S');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2011', 'A');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2010', '');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2009', '');

create table test2 (
id int,
cohort varchar(100),
term varchar(100),
class varchar(1)
);

insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2016', '');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2015', 'S');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2014', 'S');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2013', 'S');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2012', 'S');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2011', 'A');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2010', 'A');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2009', 'A');

quit;

proc sort data=test2;
by id cohort term;
run;

Re: Fill in Blanks on Last Column when Value Found

Reeza — Mon, 16 Jul 2018 17:36:04 GMT

RETAIN?

retain temp_class;
if not missing(class) then temp_class=class;
else class=temp_class;

Re: Fill in Blanks on Last Column when Value Found

DavidPhillips2 — Mon, 16 Jul 2018 17:47:38 GMT

Thanks that worked.

Re: Fill in Blanks on Last Column when Value Found

Reeza — Mon, 16 Jul 2018 17:53:08 GMT

Remember to reset at boundaries (Cohort or such) as needed. I forgot about that in the code above.

by cohort;

if first.cohort then call missing(retained_var);

Re: Fill in Blanks on Last Column when Value Found

DavidPhillips2 — Mon, 16 Jul 2018 18:17:32 GMT

I'm trying to add in an if condition to the else logic instead of generally copying the class down. I think the value from the line

else if student_classificationf in('A', 'M', 'D') then student_classification1 = student_classificationf; is saving in the retain so the other values are not set.

data enr_ready_all_enrollment; set enr_ready_all_enrollment;
by person_uid cohort;
if first.cohort then student_classification_descf = '';
retain student_classification_descf;
if not missing(student_classification1) then student_classification_descf=student_classification1;
else do;
if student_classificationf in ('J', 'R', 'S', 'B', 'N', 'F') then student_classification1='N';
else if student_classificationf in('A', 'M', 'D') then student_classification1 = student_classificationf;
else if student_classification1 = student_classificationf;
end;
run;

Re: Fill in Blanks on Last Column when Value Found

Reeza — Mon, 16 Jul 2018 18:24:31 GMT

else if student_classification1 = student_classificationf;

This line looks incorrect?

Re: Fill in Blanks on Last Column when Value Found

DavidPhillips2 — Mon, 16 Jul 2018 18:35:18 GMT

This works

proc sql;
create table test (
id int,
cohort varchar(100),
term varchar(100),
class varchar(1)
);

insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2016', '');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2015', 'M');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2014', 'S');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2013', '');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2012', 'S');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2011', '');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2010', '');
insert into test (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2009', '');

create table test2 (
id int,
cohort varchar(100),
term varchar(100),
class varchar(1)
);

insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2016', '');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2015', 'M');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2014', 'S');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2013', 'S');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2012', 'S');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2011', 'A');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2010', 'A');
insert into test2 (id, cohort, term, class) values (253303, 'Fall 2009', 'Fall 2009', 'A');

quit;

proc sort data=test2;
by id cohort term;
run;

/*fill in blanks for student classifications*/
proc sort data=test;
by id cohort descending term;
run;
data test3; set test;
by id cohort;
if first.cohort then temp_class = '';
retain temp_class;
if not missing(class) then temp_class=class;
else do;
if temp_class in ('J', 'R', 'S', 'B', 'N', 'F') then class='N';
else if temp_class in('A', 'M', 'D') then class = temp_class;
else class = temp_class;
end;
run;