Help using Base SAS procedures

Merge issue with duplicates

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

Merge issue with duplicates


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


Accepted Solutions
Solution
‎02-06-2013 10:58 AM
Respected Advisor
Posts: 3,780

Re: Merge issue with duplicates

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;

View solution in original post


All Replies
Solution
‎02-06-2013 10:58 AM
Respected Advisor
Posts: 3,780

Re: Merge issue with duplicates

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;
Regular Contributor
Posts: 168

Re: Merge issue with duplicates

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


Frequent Contributor
Frequent Contributor
Posts: 94

Re: Merge issue with duplicates

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 178 views
  • 0 likes
  • 3 in conversation