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
Make new variables to hold these condition , then MERGE.
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
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.
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;
Make new variables to hold these condition , then MERGE.
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
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
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;
?? Are you Chinese too ?
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.