Missing records

[twildone]

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

[ballardw]

I don't know how you created the "want" text but could you rebuild it with a monospace font so that things align a bit more clearly and repost it? I am having a very hard time telling the "want" from the first data step as it currently appears.

What should happen if a student changes majors? Does that have any affect on the process? or completes two or more programs and possibly graduates twice?

[twildone]

I edited the Want Table so hopefully now it is easier to see the output better. To answer your questions, if a student changes majors, then a grad date, if there is one for that program, will be entered for that major. That is why I the group by StudentUID and Major and the reason for taking the maximum of the graduation date is to makes sure that if there is a graduation date entered for the major, then at least it has been entered in the correct term and then I can deal with removing the graduation date from the incorrect term if it exist.