BookmarkSubscribeRSS Feed
lior
Calcite | Level 5

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

COMPANYDATECOMPANY RETURN
company a
company a
company a
company a
company b
company b
company b
company b
company c
company c
company c
company c
31/03/2012
30/06/2012
30/09/2012
31/12/2012
31/03/2012
30/06/2012
30/09/2012
31/12/2012
31/03/2012
30/06/2012
30/09/2012
31/12/2012
4
5
3
7
12
20
25
22
15
17
16
14

file_2

DATEMARKET_RETURN
31/03/201210
30/06/201212
30/09/201212
31/12/201211

file_combined

  COMPANYDATECOMPANY RETURNMARKET_RETURN
company a
31/03/2012
410
company a
30/06/2012
512
company a
30/09/2012
312
company a
31/12/2012
711
company b
31/03/2012
1210
company b
30/06/2012
2012
company b
30/09/2012
2512
company b
31/12/2012
2211
company c
31/03/2012
1510
company c
30/06/2012
1712
company c
30/09/2012
1612
company c
30/12/2012
1411
4 REPLIES 4
art297
Opal | Level 21

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;

lior
Calcite | Level 5

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

art297
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 8899 views
  • 0 likes
  • 2 in conversation