BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hx
Calcite | Level 5 hx
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
mahesh146
Obsidian | Level 7

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;

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20
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 

hx
Calcite | Level 5 hx
Calcite | Level 5

thank you, but what if I have duplicates? 

novinosrin
Tourmaline | Level 20

Provide us a better sample that represents your real please

novinosrin
Tourmaline | Level 20

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;
mahesh146
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 7224 views
  • 0 likes
  • 3 in conversation