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

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

lior — Sun, 16 Nov 2014 17:27:46 GMT

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

company a

company b

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

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

31/03/2012

company a

30/06/2012

company a

30/09/2012

company a

31/12/2012

company b

31/03/2012

company b

30/06/2012

company b

30/09/2012

company b

31/12/2012

company c

31/03/2012

company c

30/06/2012

company c

30/09/2012

company c

30/12/2012

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

art297 — Sun, 16 Nov 2014 18:41:26 GMT

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;

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

lior — Sun, 16 Nov 2014 19:07:29 GMT

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

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

art297 — Sun, 16 Nov 2014 19:35:56 GMT

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;

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

lior — Sun, 16 Nov 2014 20:13:39 GMT

Thanks again !.

Lior