Hi....I am trying to correct the actual term that graduation occurred. If a graduation date is recorded, that date should appear in the last term that the student was enrolled in the program. The code below works except that when the student is enrolled in the same program in more than one term and no graduation date is entered, only the record with the last term for the program is in the output. Any suggestions on how to correct this...Thanks.
data Have;
length StudentUID 8 Major $ 22 Term $ 5 GraduationDate $ 10 CompleteProgram $ 3;
format StudentUID best12. Major $char22. Term $char5. GraduationDate $char10. CompleteProgram $char3.;
informat StudentUID best12. Major $char22. Term $char5. GraduationDate $char10. CompleteProgram $char3.;
infile datalines4 dlm='7f'x missover dsd;
input StudentUID : best32. Major : $char22. Term : $char5. GraduationDate : $char10. CompleteProgram : $char3.;
datalines4;
413Electronics16-17 No
785AutoBody15-162016-06-22Yes
979Carpentry19-20 Yes
1265Electrical18-192019-06-25No
1906AutoMechanics15-16 No
1906AutoMechanics16-17 No
5571Welding17-182018-07-12Yes
5571Welding18-19 Yes
5865Drafting16-17 No
5865Drafting17-182017-07-14No
7088Welding17-182018-07-12Yes
7088Welding18-19 No
7533Drafting16-17 Yes
7533Drafting17-182017-07-14No
9892Electrical15-16 Yes
9892Electrical16-17 Yes
;;;;
proc sql noprint;
create table Want1 as
select
t1.StudentUID,
t1.Major,
t1.Term,
t1.GraduationDate,
t1.CompleteProgram,
(max(t1.GraduationDate)) as Max_GraduationDate
from work.Have t1
group by t1.StudentUID,t1.Major;
quit;
data Want1(drop=GraduationDate);
set Want1;
rename MAX_GraduationDate=GraduationDate;
run;
data Want2;
set Want1;
proc sort data=Want2 nodupkey;
by StudentUID Major Term;
run;
data Want2;
set Want2;
by StudentUID Major notsorted;
if first.Major and last.Major then output;
else
if not first.Major and missing(GraduationDate) then output;
else
if not last.Major and not missing(GraduationDate) then do;
CompleteProgram = 'No';
GraduationDate = '';
output;
end;
else
if last.Major and not missing(GraduationDate) then do;
CompleteProgram = 'Yes';
output;
end;
run;
Want:
StudentUID Major Term GraduationDate CompleteProgram
413 Electronics 16-17 No
785 Autobody 15-16 2016-06-22 Yes
979 Carpentry 19-20 Yes
1265 Electrical 18-19 2019-06-25 No
1906 AutoMechanics 15-16 No
1906 AutoMechanics 16-17 No
5571 Welding 17-18 No
5571 Welding 18-19 2018-07-12 Yes
5865 Drafting 16-17 No
5865 Drafting 17-18 2017-07-14 Yes
7088 Welding 17-18 No
7088 Welding 18-19 2018-07-12 Yes
7533 Drafting 16-17 No
7533 Drafting 17-18 2017-07-14 Yes
9892 Electrical 15-16 Yes
9892 Electrical 16-17 Yes
... View more
