topic Re: merge by date and date range in SAS Programming

merge by date and date range

hx — Sat, 19 May 2018 19:32:09 GMT

I have two datasets

cusip6	date	nation
123r	5/8/2015	us
a234	5/8/2010	us

cusip6	begin	end	price
123r	5/8/2013	5/8/2016	13
123r	5/8/2010	5/7/2013	9

I want to merge them and get the following results.

cusip6	date	nation	price
123r	5/8/2015	us	13

Can anyone help me?

Re: merge by date and date range

novinosrin — Sat, 19 May 2018 19:46:04 GMT

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

Re: merge by date and date range

hx — Sat, 19 May 2018 19:57:55 GMT

thank you, but what if I have duplicates?

Re: merge by date and date range

novinosrin — Sat, 19 May 2018 20:12:20 GMT

Provide us a better sample that represents your real please

Re: merge by date and date range

novinosrin — Sat, 19 May 2018 20:17:36 GMT

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;

Re: merge by date and date range

mahesh146 — Tue, 22 May 2018 07:49:34 GMT

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;