DATA Step, Macro, Functions and more

merge by date and date range

Reply
Occasional Contributor hx
Occasional Contributor
Posts: 19

merge by date and date range

I have two datasets

 

cusip6datenation
123r5/8/2015us
a2345/8/2010us

 

cusip6beginendprice
123r5/8/20135/8/201613
123r5/8/20105/7/20139

I want to merge them and get the following results.

cusip6datenationprice
123r5/8/2015us13

Can anyone help me?

PROC Star
Posts: 1,593

Re: merge by date and date range

[ Edited ]
data one;
input cusip6 $	date :mmddyy10.	nation $;
format date mmddyy10.;
cards;
123r	5/8/2015	us
a234	5/8/2010	us
;
data two;

input cusip6$	(begin	end) (:mmddyy10.)	price;
format begin	end mmddyy10.;
cards;
123r	5/8/2013	5/8/2016	13
123r	5/8/2010	5/7/2013	9
;

data want;
merge one(in=a) two(in=b) ;
by cusip6;
if a and b and begin<date<end then output;
run;

 This assumes your cusip6 in one is a primary key meaning no duplicates 

Occasional Contributor hx
Occasional Contributor
Posts: 19

Re: merge by date and date range

Posted in reply to novinosrin

thank you, but what if I have duplicates? 

PROC Star
Posts: 1,593

Re: merge by date and date range

Provide us a better sample that represents your real please

PROC Star
Posts: 1,593

Re: merge by date and date range

Posted in reply to novinosrin

or use sql:

 

proc sql;
create table want as
select a.*,price
from one  a inner join two b
on a.cusip6=b.cusip6 and begin<date<end ;
quit;
Occasional Contributor
Posts: 15

Re: merge by date and date range

DATA TEMP;
INPUT ID date Nation $;
INFORMAT DATE mmddyy10.;
FORMAT DATE date9.;
DATALINES;
1 02/25/2016 USA
2 05/12/2015 UK
3 11/05/2018 INDIA
1 06/20/2018 Spain
;
RUN;

DATA TEMP_RANGE;
INPUT ID ST_DT END_DT VAL;
INFORMAT ST_DT END_DT ddmmyy10.;
FORMAT ST_DT END_DT date9.;
CARDS;
1 01/01/2015 31/12/2017 100
1 01/01/2018 31/12/2018 200
2 25/10/2014 31/12/2015 50
3 15/05/2015 30/11/2018 150
;
RUN;

 

PROC SQL;
CREATE TABLE TEMP_FINAL AS
SELECT
A.*,
B.ST_DT,
B.END_DT,
B.VAL
FROM
TEMP A
LEFT JOIN
TEMP_RANGE B
ON
A.ID=B.ID
AND
B.ST_DT<=A.DATE<=B.END_DT
;

TITLE 'Final Table';
SELECT * FROM TEMP_FINAL;
QUIT;

Ask a Question
Discussion stats
  • 5 replies
  • 160 views
  • 0 likes
  • 3 in conversation