topic Re: Merge Multiple records per subject in SAS Programming

Merge Multiple records per subject

Nasya — Thu, 21 Jun 2018 18:12:15 GMT

Hi,

Can some one help me to get rid of this note. "NOTE: MERGE statement has more than one data set with repeats of BY values."

The following is the part of the code that is being written merging the following data sets with varied number of observations.

data abc_;
set abc;
run;
proc sort data=abc_;
by SUBJECT;
run;

data efg_;
set efg;
run;
proc sort data=efg_;
by SUBJECT;
run;

data xyz_;
set xyz;
run;
proc sort data=xyz_;
by SUBJECT;

proc sort data=der3_;
by SUBJECT;
run;

data der4;
merge der3_ (in=a) abc_ (in=b) efg_ (in=c) xyz_ (in=d);
by SUBJECT;
if c;;
run;

LOG:

NOTE: There were 83 observations read from the data set WORK.DER3_.

NOTE: There were 180 observations read from the data set WORK.ABC_.

NOTE: There were 728 observations read from the data set WORK.EFG_.

NOTE: There were 89 observations read from the data set WORK.XYZ_.

NOTE: The data set WORK.DER4 has 728 observations and 35 variables.

As a note, work.abc_ data set has multiple records per subject, as test results are recorded starting from Day 0- Day7, therefore each subject has 8 records. SUBJECT is the unique ID for all the data sets. How to handle the multiple records when merging? Any suggestion is highly appreciated.

Thank you,

Regards,

Nasya

Re: Merge Multiple records per subject

Astounding — Thu, 21 Jun 2018 18:30:09 GMT

The note is telling you that either:

(1) You're wrong and one of the other data sets contains multiple records for a SUBJECT. (You would have to locate where that happens.)

(2) SUBJECT is a character variable with different lengths across the data sets. There are easy fixes if you know that this is the problem.

Re: Merge Multiple records per subject

Nasya — Thu, 21 Jun 2018 18:46:15 GMT

#2 is not the issue but can be #1.
When you say, locate where it happens, do you mean the data set which has the multiple records or the variable?

Re: Merge Multiple records per subject

Astounding — Thu, 21 Jun 2018 18:52:00 GMT

Yes, which data set (besides the one that you know about) contains multiple records per SUBJECT. Since your PROC SORTs all use a temporary copy of the original data, you could just add NODUPKEY to each PROC SORT. The log will identify how many observations get removed.

Once you find out where the problem is, you need to decide what to do, to correct the data. It's mostly a decision, not a programming problem.