Merging datasets and duplicate observations

[PSU_Sudzi]

Hello Folks,

I am creating a listing merging all Med History, Con Meds, and AEs for manual review to catch items that may be recorded in one place but missing in others, e.g. an Indication for Con Meds that is missing from either MH or AE or a worsening AE missing an MH entry with original diagnosis. It came out nicely but because the only item in common I have with all 3 tables is the subject number, it will duplicate some items. In a small example of the output, you can see that MEDICATION #2 is duplicated in the output since there are 4 AEs but only 2 Con Meds. Is there a way to modify my code to remove the additional entries of MEDICATION #2 on the output, so that it would only appear the first time and then be blank? Code posted below.

options mergenoby=error;
data study1.mhcmae;
merge study1.mh study1.cm study1.ae;
by subj;
run;

Medication	Indication	Adverse Event
MEDICATION #1	INDICATION #1	AE #1
MEDICATION #2	INDICATION #2	AE #2
MEDICATION #2	INDICATION #2	AE #3
MEDICATION #2	INDICATION #2	AE #4

[Tom]

That is how SAS does a merge. If one of the datasets stops contributing observations the values of the variables unique to that dataset never get changed.  That is why 1 to Many merges work well.

For your report you can just use CALL MISSING() to reset ALL of the variables.  Then the datasets that are still contributing will update their variables and the variables from the ones that have run out will remain missing. 

Remember to write the current observation first.

data study1.mhcmae;
  merge study1.mh study1.cm study1.ae;
  by subj;
  output;
  call missing(of _all_);
run;

[PSU_Sudzi]

Hi Tom, thanks so much, this makes the output look 10 times better! You're help is appreciated.