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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: