BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidPhillips2
Rhodochrosite | Level 12

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

RETAIN?

 

 

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

 

 

View solution in original post

6 REPLIES 6
Reeza
Super User

RETAIN?

 

 

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

 

 

DavidPhillips2
Rhodochrosite | Level 12

Thanks that worked.

Reeza
Super User

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

DavidPhillips2
Rhodochrosite | Level 12

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;

Reeza
Super User

else if student_classification1 = student_classificationf;

 

This line looks incorrect?

DavidPhillips2
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 749 views
  • 0 likes
  • 2 in conversation