## How to merge two data sets with time difference?

Solved
Occasional Contributor
Posts: 12

# How to merge two data sets with time difference?

Hey, SASers

I hope you can help me on this issue. I do not know how to merge data in this kind of situation.

-------------

data set 1

year    month    ticker    price

1990      1          AAA       22

1990      2         AAA       23

1990      3         AAA       21

1990      4          AAA       28

data set 2

year    month    ticker      holding

1989      12        AAA       77

1990      3         AAA       53

-----------------

I want to create merge data set 2 into data set 1.

However, month 1-3 in data set 1 will take the value of the month 12 of last year in data set 2, and month 4-6 of month 3 this year, and month 7-9 of month 6 this year, and month 10-12 of month 9 this year.

So the output in my mind is:

year    month    ticker    price    holding

1990      1          AAA       22      77

1990      2         AAA       23       77

1990      3         AAA       21       77

1990      4          AAA       28      53

Cheers,

James

Accepted Solutions
Solution
‎06-28-2015 12:35 AM
Super User
Posts: 10,784

## Re: How to merge two data sets with time difference?

Make new variables to hold these condition , then MERGE.

### Code: Program

`data set_1;infile datalines truncover dlm=' ' dsd;input year month ticker \$ price;datalines;1990 1 AAA 221990 2 AAA 231990 3 AAA 211990 4 AAA 28;run; data set_2;infile datalines truncover dlm=' ' dsd;input year month ticker \$ holding;datalines;1989 12 AAA 771990 3 AAA 53;run;data set_1; set set_1; if month in (1:3) then do;_year=year-1;_month=12;end;  else if month in (4:6) then do;_year=year;_month=3;end;   else if month in (7:9) then do;_year=year;_month=6;end;   else if month in (10:12) then do;_year=year;_month=9;end;run;data want; merge set_1(in=ina) set_2(rename=(year=_year month=_month)); by ticker  _year _month; if ina;drop _:;run;`

Xia Keshan

All Replies
Occasional Contributor
Posts: 12

## Re: How to merge two data sets with time difference?

And another problem is that,,, not every stock (denoted by TICKER) or at every time point , has a holding data.

So if it is none ,  I would like to assign 0 to its holding.

This is really complicated for a newbie like me.

Thanks you guys for helping me.

Posts: 4,736

## Re: How to merge two data sets with time difference?

Working with dates things become normally really easy as soon as you're using actual SAS Dates. A SAS Date is a number stored in a numeric variable which contains the count of days since 1/1/1960.

Once you've got such a SAS Date you can use SAS calendar functions to modify dates.

In the code below:

Function MDY() uses your source data to construct a SAS Date value  SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition

Once we've got a SAS Date value function INTNX() allows to increment a SAS Date value by a given interval SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition

So for your case using "intnx()" for "set_1" we get the beginning of the previous quarter of the date, and for "set_2" we get the beginning of the current quarter of the date. Now the two dates are aligned and we can merge/join the two tables where the two dates match.

For cases with no matching record in "set_2": Using a left join we won't loose any records from "set_1" but "holding" will be missing. A coalesce() function picks the first non-missing value so by using "coalesce(holding,0)" whenever "holding" is missing we will select "0". SAS(R) 9.4 SQL Procedure User's Guide

data set_1;

infile datalines truncover dlm=' ' dsd;

input year month ticker \$ price;

format date date9.;

date=mdy(month,1,year);

format match_date date9.;

match_date=intnx('quarter',date,-1,'b');

datalines;

1990 1 AAA 22

1990 2 AAA 23

1990 3 AAA 21

1990 4 AAA 28

1990 10 AAA 28

;

run;

data set_2;

infile datalines truncover dlm=' ' dsd;

input year month ticker \$ holding;

format date date9.;

date=mdy(month,1,year);

format match_date date9.;

match_date=intnx('quarter',date,0,'b');

datalines;

1989 12 AAA 77

1990 3 AAA 53

;

run;

proc sql;

create table want as

select

s1.*

, coalesce(s2.holding,0) as holding

from

set_1 s1 left join set_2 s2

on

s1.ticker=s2.ticker

and s1.match_date=s2.match_date

;

quit;

Solution
‎06-28-2015 12:35 AM
Super User
Posts: 10,784

## Re: How to merge two data sets with time difference?

Make new variables to hold these condition , then MERGE.

### Code: Program

`data set_1;infile datalines truncover dlm=' ' dsd;input year month ticker \$ price;datalines;1990 1 AAA 221990 2 AAA 231990 3 AAA 211990 4 AAA 28;run; data set_2;infile datalines truncover dlm=' ' dsd;input year month ticker \$ holding;datalines;1989 12 AAA 771990 3 AAA 53;run;data set_1; set set_1; if month in (1:3) then do;_year=year-1;_month=12;end;  else if month in (4:6) then do;_year=year;_month=3;end;   else if month in (7:9) then do;_year=year;_month=6;end;   else if month in (10:12) then do;_year=year;_month=9;end;run;data want; merge set_1(in=ina) set_2(rename=(year=_year month=_month)); by ticker  _year _month; if ina;drop _:;run;`

Xia Keshan

Occasional Contributor
Posts: 12

## Re: How to merge two data sets with time difference?

Keshan, Thanks so much!    I will definitely buy you a dinner when coming back to Beijing.

Another question followed up is that the set_2 is not always there. Say, I get set_1 data '1990 7 AAA 22', but since there is no holdings at 1990 6 for AAA, so there is no corresponding point in set_2. In this case, I would like to assign 0 to set_1.

I am thinking about use UPDATE to do this, but I cannot quite realize it.

Best,

James

Posts: 4,736

## Re: How to merge two data sets with time difference?

Sticking with using proper SAS Date values and calendar functions here the data step/merge options instead of a SQL:

data want;

merge set_1 (in=in1) set_2;

by ticker match_date;

if in1;

holding=coalesce(holding,0);

run;

Super User
Posts: 10,784

## Re: How to merge two data sets with time difference?

?? Are you Chinese too ?

### Code: Program

`data set_1;infile datalines truncover dlm=' ' dsd;input year month ticker \$ price;datalines;1990 1 AAA 221990 2 AAA 231990 3 AAA 211990 4 AAA 281990 7 AAA 22;run; data set_2;infile datalines truncover dlm=' ' dsd;input year month ticker \$ holding;datalines;1989 12 AAA 771990 3 AAA 53;run;data set_1; set set_1; if month in (1:3) then do;_year=year-1;_month=12;end;  else if month in (4:6) then do;_year=year;_month=3;end;   else if month in (7:9) then do;_year=year;_month=6;end;   else if month in (10:12) then do;_year=year;_month=9;end;run;data want; merge set_1(in=ina) set_2(in=inb rename=(year=_year month=_month)); by ticker  _year _month; if ina and not inb then holding=0; if ina;drop _:;run;`
🔒 This topic is solved and locked.