How to merge two data sets with time difference?

[Laoban_James]

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

[Laoban_James]

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.

[Patrick]

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;

[Ksharp · Accepted solution]

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

[Laoban_James]

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

[Patrick]

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;

[Ksharp]

?? 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;