Hellow, I'm facing a problem merging two files as follow:
In file_1 I have several companies , for each company I have the Company Return of each querter.
In file_2 I have only 4 items (MARKET_RETURN of each querter).
I would like to merge the two files in order to recieve a file_combined that presents near each Company Return the Market Return for the same quarter.
In a way its like a vlookup function in the excel.
In the actual research the details are more complicated so I can't apply "IF statements" .
I belive I need to apply "Joins statement" but I have no succuss in writing the code in the right way...
Thanks a lot, Lior
file_1
COMPANY | DATE | COMPANY RETURN | ||||||||||||||||||||||||||||||||||||
|
|
|
file_2
DATE | MARKET_RETURN |
31/03/2012 | 10 |
30/06/2012 | 12 |
30/09/2012 | 12 |
31/12/2012 | 11 |
file_combined
COMPANY | DATE | COMPANY RETURN | MARKET_RETURN | ||
company a |
| 4 | 10 | ||
company a |
| 5 | 12 | ||
company a |
| 3 | 12 | ||
company a |
| 7 | 11 | ||
company b |
| 12 | 10 | ||
company b |
| 20 | 12 | ||
company b |
| 25 | 12 | ||
company b |
| 22 | 11 | ||
company c |
| 15 | 10 | ||
company c |
| 17 | 12 | ||
company c |
| 16 | 12 | ||
company c |
| 14 | 11 | ||
Sounds like you simply want to do a left join on the files. e.g.:
proc sql;
create table file_combined as
select a.*,b.market_return
from file_1 a
left join file_2 b
on put(a.date,qtr.) eq put (b.date,qtr.)
order by company, date
;
quit;
Hi Arthur,
Thanks for the quick answer.
As I'm only doing my first steps at SAS I'm not farmiliar with this code and therfore have not succeeded to apply it on my original data files.
do you by any chance has a link to some explanation of this code (for example what the "a.*,b stand for).
Thank you very much,
Lior
Easier to describe than provide links. Hopefully, the following comments provide enough direction:
/*start proc sql*/
proc sql;
/*create file called file_combined*/
create table file_combined as
/* */
/*select the variables to include in file_combined, namely all of the variables from file1 (i.e. a.* */
/*and market_return from file2 (i.e., b. */
select a.*,b.market_return
/* */
/*indicate that file_1 will be referred to as a */
from file_1 a
/* */
/*indicate that file_2 will be referred to as b and should be joined with a. Left join is used so that*/
/*there will only be records matching those in file_1 */
left join file_2 b
/* */
/*only do the join when the dates in file_1, expressed as quarters, match the dates in */
/*file_2 (expressed as quarters) */
on put(a.date,qtr.) eq put (b.date,qtr.)
/* */
/*order the resulting file (i.e., file_combined) are in the order of date within company */
order by company, date
/* */
/*indicate the end of the proc sql statements */
;
/* */
/*terminate proc sql */
quit;
Thanks again !.
Lior
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.