Merge statement has more than one data set with repeats of BY values

[smackerz1988]

Hello I have this code that is doing as intended but I get this warning error in my log. How do I workaround that? I basically want maxrecdate and maxrec to merge to vischk1 by usubjid and adt for further processing but without it altering the current version of my data in totedps1_pre with the merge statement applied

proc sql;
   create table maxrec1 as
   select distinct usubjid,  
                   adt, 
                   adt as maxrecdate,
                   aval as maxrec,
                   visit,                                    
                
   from vischk1
   where aval ne . and visit not in('Screening','Day 1','Week 1') and max(swrel,swnrel,t2cm,rtt1l28d)=0
   order by usubjid,paramcd,adt,visit;
   run;


 data totedps1_pre;  
 merge vischk1 maxrec1;
 by usubjid adt; 
 run;

[ballardw]

I would very carefully manually follow the result of the Merge. If the two merged sets do not have exactly the same number of records going into the data step for every by group combination I doubt that the result is as actually wanted every time.

That warning is there with the Merge because very seldom does a data step merge with multiple values if the by variables work as intended. The data step merge is designed to work with one, or neither, data set with repeats of the by variable values. When both have multiples the result is usually truncated to matching the shorter list so some repeats of one set are not even in the output.

Some questions: Why are you ordering your output in Proc SQL by a variable that in not in the output?

[smackerz1988]

The PARAMCD is in the output as it is in vischk1. the visit is to account for Early termination visits that occur by date that satisfy the criteria. Would an ALTER table to add maxrec,maxrecdate here work or an alternative to merge like a PROC SQL join?. I think the issue is I have to retain adt and maxrec because If I try to merge by usubjid maxrec it states that maxrec is not in vischk and if I remove adt from maxrec and merge by usubjid adt I get the same for maxrec. Here is the current log

"

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
NOTE: Compressing data set WORK.MAXREC1 increased size by 50.00 percent.
Compressed is 3 pages; un-compressed would require 2 pages.
NOTE: Table WORK.MAXREC1 created, with 969 rows and 6 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: There were 2493 observations read from the data set WORK.VISCHK1.
NOTE: There were 969 observations read from the data set WORK.MAXREC1.
NOTE: The data set WORK.TOTEDPS1_PRE has 2493 observations and 113 variables.
NOTE: Compressing data set WORK.TOTEDPS1_PRE decreased size by 67.21 percent.
Compressed is 20 pages; un-compressed would require 61 pages.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds"

[smackerz1988 · Accepted solution]

Did it this way instead and it works thankfully

 proc sql;
 create table totedps1_pre as
   select a.*,
          b.maxrecdate,b.maxrec 
   from vischk1 a full join maxrec1 b on a.usubjid=b.usubjid and                                     
                                         a.adt=b.maxrecdate and
							             a.visit=b.visit;
quit;

lly