topic Merge issue with duplicates in SAS Procedures

Merge issue with duplicates

sam369 — Wed, 06 Feb 2013 15:38:52 GMT

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

Re: Merge issue with duplicates

data_null__ — Wed, 06 Feb 2013 15:58:09 GMT

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; 

Re: Merge issue with duplicates

VD — Wed, 06 Feb 2013 16:05:48 GMT

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;

Re: Merge issue with duplicates

sam369 — Wed, 06 Feb 2013 16:19:35 GMT

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