correct entry

[twildone]

Hi...I am trying to correct the withdrew entries for the student in the same program. The correction is based on the last record for the student in the same program and is when there are multiple records for the same student and program, the last record with a Withdrew entry is retained and all previous records are to have missing entries for Withdrew. if the Withdrew entry in the last record for the same student and program is missing then all Withdrew entries should be missing otherwise the last Withdrew entry should be retained. Not sure how to do this. Any suggestions would be greatly appreciated. Thanks.

data have;                                                                                                                                      
  input Student $ (start completion withdrew) (:6.) Program $30. ;                                                                                              
  datalines4;  
A  201509 201606 201603 AutoMechanics
A  201609 201706 201704 AutoMechanics
A  201809 201906 . AutoMechanics
A  201909 202006 . Carpentry
B  201509 201606    .    AutoMechanics
B  201609 201706 201703 AutoMechanics
B  201709 201806 . AutoMechanics
B  201809 201906 201905 AutoMechanics
;;;;

want:
A  201509 201606 . AutoMechanics
A  201609 201706 . AutoMechanics
A  201809 201906 . AutoMechanics
A  201909 202006 . Carpentry
B  201509 201606 . AutoMechanics
B  201609 201706 . AutoMechanics
B  201709 201806 . AutoMechanics
B  201809 201906 201905 AutoMechanics

[ed_sas_member · Accepted solution]

Hi @twildone 

Does this code meet your expectations?

I am not sure to have well understood the rule to correct the withdrew entry.

Best,

data ref;
	set have;
	by Student Program;
	if last.Program then do;
		rename withdrew=withdrew_ref;
		output;
	end;
	keep Student Program withdrew;
run;

data want;
	merge have ref;
	by Student Program;
	if withdrew_ref ne . and not last.Program then withdrew= .;
	drop withdrew_ref;
run;

[twildone]

Hi Ed_Sas_Member....Yes your code did what I had originally had asked for. I had made a mistake and is probably why you might have been confused...my apology. The first student should have all missing Withdrew entries because the last record had a missing Withdrew entry...I made the correction and apology again.....Thanks

[twildone]

Hi Ed_Sas_Member....thanks for your help and suggestion. With your help and by adding the following statement I was able to get what I wanted.....Thanks.

if withdrew_ref = . and not last.Program and withdrew ne . then withdrew= .;

[ed_sas_member]

Hi @twildone 

No problem