DATA Step, Macro, Functions and more

finding a SAS function that perform like vlookup function in the excel

Reply
Contributor
Posts: 38

finding a SAS function that perform like vlookup function in the excel

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
PROC Star
Posts: 7,491

Re: finding a SAS function that perform like vlookup function in the excel

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;

Contributor
Posts: 38

Re: finding a SAS function that perform like vlookup function in the excel

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

PROC Star
Posts: 7,491

Re: finding a SAS function that perform like vlookup function in the excel

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;

Contributor
Posts: 38

Re: finding a SAS function that perform like vlookup function in the excel

Thanks again !.

Lior

Ask a Question
Discussion stats
  • 4 replies
  • 1373 views
  • 0 likes
  • 2 in conversation