topic Re: Lookup and Merging in SAS Procedures

Lookup and Merging

deleted_user — Tue, 23 Mar 2010 12:26:05 GMT

I have two tables.
The first table contains:
ISIN | Stock | Dividend Date

The second table contains the stock returns of all the stocks in a country over a time horizon of several years.
ISIN | Stock Price

I want to merge the two. The merged table should give me:
ISIN | Stock | Dividend Date | Stock Price on Dividend Date

So I'd have to first ensure that the ISIN of the company matches, and then I need SAS to look up the stock price only on the dividend date.
I'm not really sure how to program it. Can anyone help?
Thank you.

Re: Lookup and Merging

sbb — Tue, 23 Mar 2010 12:57:06 GMT

Investigate using SAS PROC SQL and JOIN processing to relate the two file sources.

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search argument, this topic/post:

sql procedure join processing merge site:sas.com

Re: Lookup and Merging

deleted_user — Tue, 23 Mar 2010 13:05:28 GMT

Thanks Scott.
Can you be a bit more specific? I'm not very familiar with SAS and already looked at lots of examples in the help files, but still get error messages.

Re: Lookup and Merging

LinusH — Tue, 23 Mar 2010 13:20:48 GMT

To be more specific:
IF you chose to use data step and MERGE, use IN= option together with a subsetting if, e.g.:
IF a AND b;

If you use SQL, specify an SQL with an inner join.

If you have code errors, please attach them together with your code, then could get some more specific help.

This is considered as quite basic programming. If you will continue to use SAS, I encourage you to take some SAS classes (programming and SQL).

/Linus

Re: Lookup and Merging

RPGarland — Tue, 23 Mar 2010 13:23:58 GMT

Tina,

I see that the second table contains the identification number and stock price but its over several years, is there a date associated with this table?

If you are not quite familiar with SQL, you can use multiple datasteps:

proc sort data=table1;
by isin;
run;

proc sort data=table2;
by isin;
run;

data combined;
merge table1 table2;
by isin;
run;

***This data contains only those that merge and match isin;
data matching;
set combined;
if nmiss(isin,stock,divdt,stockprice)=0;
run;
proc print data=matching; title1 'Matching'; run;

***If you need the set with the differences, use this;
data diffs;
set combined;
if nmiss(isin,stock,divdt,stockprice)>0;
run;
proc print data=diffs; title1 'Differences'; run;

Hope this helps!

Re: Lookup and Merging

deleted_user — Tue, 23 Mar 2010 13:40:53 GMT

Thanks a lot! You're right, in table 2 I have the dates and stock prices. When I merge the two tables using 'merge' function, I obtain all stock prices for a particular company over time. However, I only want the stock price on the particular date that I had specified in table 1. How can I incorporate that?
Thanks!

Re: Lookup and Merging

RPGarland — Tue, 23 Mar 2010 14:22:17 GMT

Tina,

It would help if you put some sample code or how the variables were named. But if the date variables are the same, you can add the date variable to the sort statements and the merge statement.

proc sort data=table1;
by isin DIVDT;
run;

proc sort data=table2;
by isin DIVDT;
run;

data combined;
merge table1 table2;
by isin DIVDT;
run;

***This data contains only those that merge and match isin;
data matching;
set combined;
if nmiss(isin,stock,divdt,stockprice)=0;
run;
proc print data=matching; title1 'Matching'; run;

***If you need the set with the differences, use this;
data diffs;
set combined;
if nmiss(isin,stock,divdt,stockprice)>0;
run;
proc print data=diffs; title1 'Differences'; run;

That should help. If the date variables are different, you neet to rename a variable to match the variable that you are using to merge.

Re: Lookup and Merging

deleted_user — Tue, 23 Mar 2010 14:43:02 GMT

Thanks.
Ok, I have in table A:
ISIN | Issuer | DivDate

In table B, I have:
ISIN | Issuer | TradeDate | Stock Price

Table B is a very large file with stock prices for many companies on each trading day.
I sorted both tables by ISIN. Now I want to combine the two table. In the combined table I want to have:
ISIN | Issuer | DivDate | Stock Price

So the combined table should only show me the stock price of a particular company on the dividend date. In Excel, I would use LOOKUP function, so only if TradeDate = DivDate do I want to get a result. But I'm not sure what I have to do in SAS.
So far, I sorted both tables by ISIN, and I will also merge by ISIN, but I need the conditional statement TradeDate=DivDate. All other observations from Table 2 should not appear in the new table.
I hope that makes it more clear.

Thanks!

Re: Lookup and Merging

sbb — Tue, 23 Mar 2010 15:02:01 GMT

You will need to be more precise with SAS - there is no fuzzy MERGE. My suggestion is that you "interleave" instead, using a SET with a BY and correlate the two files' SAS variables possibly using a RETAIN statement (with temporary variables) and carry forward data variables. You will want to read-about and use the BY GROUP PROCESSING concept with the SET / BY statement combination in conjunction with IF FIRST. and also possibly IF LAST. to perform your data association and analysis.

Another option is to identify the source data variable values you need to analyze (in your smaller file) and then load a HASH table for in-memory data variable comparisons, where you can use GT / GE / LT / LE / etc.

As was mentioned, you're new to SAS and it's going to be important to delve into the SAS Base documentation and also make use of the SAS support http://support.sas.com/ website's reference materials, including sample code, topic-related papers, and SAS-hosted documentation.

Scott Barry
SBBWorks, Inc.

Google advanced search arguments, this topic/post:

table lookup site:sas.com

data step programming by group processing site:sas.com

data step hash object site:sas.com

Re: Lookup and Merging

FredrikE — Tue, 23 Mar 2010 15:23:02 GMT

Is it not as easy as an inner join?

proc sql;
create table newtable as select a.*,b.StockPrice from
(select * from table1) as a
inner join
(select * from table2) as b
on a.ISIN = b.ISIN
and a.Issuer = b.Issuer
and a.DivDate = b.TradeDate
;
quit;

Re: Lookup and Merging

deleted_user — Tue, 23 Mar 2010 15:32:19 GMT

Thanks Scott. I know that I have to read more about the SAS Basics, unfortunately I don't have time to do that for this project as deadline approaches soon. Now, given that it looks like that there are many different ways to program in SAS and my dataset is quite large which doesn't allow me to doublecheck the results one-by-one, I need to make sure that the statement I use is correct. I already looked at lots of documents decribing the MERGE function, but there's no concrete example to what I need, mainly it's only merging by one or several variable, nothing about merging conditional or lookup. Also, the data observations in Table 1 should not decrease. So if, for example, Table 2 does not show a stock price on all Trading Dates and it effects the merging in a way that I don't have a stock price from Table 2 for the DivDate in Table 1, Table 1 should still keep the record of the stock, but then show for the stock price on the div date '.'.

Re: Lookup and Merging

sbb — Tue, 23 Mar 2010 16:22:09 GMT

Given the "pickle" you're in, I'd recommend a PROC SQL approach with focus on JOIN as a means to correlate your inputs. There are ways to code inner/nested queries so to optimize the "transaction file processing" you need to perform, given the large input.

You would benefit from generating a "candidate data" subset file, saving it to a permanent SAS data library (with the LIBNAME statement), and then work with that data sample to develop a SAS program using PROC SQL that solves your information analysis requirement.

Most definitely, return to the forum as you work through the process, to address any particular problem you may encounter. At some point, you will want and need to own your SAS programming experience, if not more than to be able to support the code going forward.

Scott Barry
SBBWorks, Inc.

Re: Lookup and Merging

deleted_user — Tue, 23 Mar 2010 16:25:41 GMT

Thanks a lot, this looks like I was looking for.
Just have to figure out the syntax at the start now as I still get error messages I typed:

proc sql;
create table mw.newtable
as select divdates.*, mktdates.prices,
from mw.divdates,
inner join mw.mktdates
on divdates.isin = mktdates.isin
and divdates.divdate = mktdates.mktdate;
quit

I suspect some errors at the start, but am not sure what's wrong.

Re: Lookup and Merging

sbb — Tue, 23 Mar 2010 17:44:20 GMT

It's a good idea to share your SAS log (pasted in a reply) with the exact error messages, so others don't spend time guessing your problem.

And, if/when you paste code, sometimes you should follow guidelines at the link below (recommend bookmarking it as a favorite for future posts):

http://support.sas.com/forums/thread.jspa?messageID=27609

Scott Barry
SBBWorks, Inc.