topic Re: How to merge two data sets with time difference? in SAS Procedures

How to merge two data sets with time difference?

Laoban_James — Sat, 27 Jun 2015 13:05:24 GMT

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

Please ask me if I am not clear enough.

Thanks in advance,

Cheers,

James

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

Laoban_James — Sat, 27 Jun 2015 16:00:51 GMT

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.

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

Patrick — Sat, 27 Jun 2015 23:51:40 GMT

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

s1.ticker=s2.ticker

and s1.match_date=s2.match_date

;

quit;

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

Ksharp — Sun, 28 Jun 2015 04:35:18 GMT

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 22
1990 2 AAA 23
1990 3 AAA 21
1990 4 AAA 28
;
run;
 
data set_2;
infile datalines truncover dlm=' ' dsd;
input year month ticker $ holding;
datalines;
1989 12 AAA 77
1990 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

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

Laoban_James — Wed, 01 Jul 2015 10:21:55 GMT

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

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

Patrick — Wed, 01 Jul 2015 11:41:58 GMT

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;

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

Ksharp — Wed, 01 Jul 2015 12:01:06 GMT

?? Are you Chinese too ?

Code: Program

data set_1;
infile datalines truncover dlm=' ' dsd;
input year month ticker $ price;
datalines;
1990 1 AAA 22
1990 2 AAA 23
1990 3 AAA 21
1990 4 AAA 28
1990 7 AAA 22
;
run;
 
data set_2;
infile datalines truncover dlm=' ' dsd;
input year month ticker $ holding;
datalines;
1989 12 AAA 77
1990 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;