Merge issue with duplicates

[sam369]

Hi All,

I have a question on merging when data have duplicate records on the same date. I have thousands of records. Find the sample data below

Data one;

Pid               desc            trig_date

100               xxxx              19JAN2009

100        yyyy       19jan2009

100        zzzz       17MAY2010

100        bbbb       18OCT2010

100        loss       19OCT2010

Data two;

Pid   smpdt

100   19jan2009

100   17MAY2010

100    18OCT2010

Data want;

Pid               desc            trig_date                   smpdt

100               xxxx              19JAN2009        19jan2009

100        yyyy       19jan2009

100        zzzz       17MAY2010       17MAY2010

100       bbbb      18OCT2010       18OCT2010

100        loss       19OCT2010

Thanks

Sam

[data_null__ · Accepted solution]

I'm not sure I understand the question.

data one;
   input Pid desc :$4. trig_date:date.;
   format t: date9.;
   cards;
100        xxxx       19JAN2009
100        yyyy       19jan2009
100        zzzz       17MAY2010
100        bbbb       18OCT2010
100        loss       19OCT2010
;;;;
   run;

Data two;
   input Pid  smpdt:date.;
   format s: date9.;
   cards;
100   19jan2009
100   17MAY2010
100   18OCT2010
;;;;
   run;

data new;
   merge one two(in=in2 rename=smpdt=trig_date);
   by pid trig_date;
   if in2 and  first.trig_date then smpdt=trig_date;
   format smpdt date9.;
   run;

[sam369]

Hi Data _null_

You understand the question right. And you gave the right one. Thanks for your help.

i am using lenthy code using retain statment, because date one have few records on trigdt and data two have few records on sampdt . But i need both record in my final dataset.Now i got it with your help

Thanks

Sam

[VD]

proc sql; create table want as select  one.*, two.smpdt from one full join two on one.trig_date=two.smpdt and one.pid=two.pid ; quit;