SAS Connection to ODBC databases
Posted: 20 October 2011 02:49 PM   [ Ignore ]
Newbie
Rank
Total Posts:  4
Joined  2011-10-20

I currently have several databases that I have created from data outside of WRDS (e.g., based on EDGAR 10-Ks). I used PERL to extract the data and then put the data into a SQLite database. I used to just import these tables into SAS but was introduced to a way to access that data directly using an ODBC connection (by Andy Leone).

There are some good tutorials out there (http://www.lexjansen.com/wuss/2004/data_warehousing/c_dwdb_using_proc_sql_and_od.pdf) on how to setup ODBC access and use it in SAS. But you essentially setup an User DSN under Control Panel…ODBC Data Sources. You specify the path to your database file and the driver (in my case SQLite 3 but it would work for other databases like MySQL) and then create a name to reference it (e.g., Edgar.db).

Then in SAS you create a library reference to the database using the following code, “libname edgar odbc dsn=Edgar.db;” and then reference the “edgar” library just like any other SAS library (e.g., data edgar.tenks; to reference the table “tenks” in my Edgar database). You can even create new tables in that database but be careful because you can also delete tables, too.

I have found it very helpful. Some researchers I have interacted with use this method to access all of their datasets, which they store using a DBMS like MySQL.

Profile
 
 
Posted: 20 October 2011 04:24 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

Thanks for you post!
I have given it a shot some tie ago to add mySql to SAS through ODBC. I didn’t get it to work at that point, but I will give it another shot. (I have just exported to .csv and insert to mySql tables with PHP)

I will be adding a perl section to the forums. I have also some SAS macros to build the SEC filing history that I will post soon.
Zenghui is eager to learn perl so you have a modest but motivated audience here smile

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 20 October 2011 06:18 PM   [ Ignore ]   [ # 2 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

dreppen,

Thanks a lot for the information!

Perl—> 10ks—> DBMS—> SAS
This seems very promising method for data management like this.

Perl—> 10ks—> spread sheet—> SAS
May i ask what is the benifit of using DBMS (SQL, SQL lite), instead of excel spread sheet, in this situation?

Secondly, like Joost said, i am eager to learn how to use Perl to extract information from 10ks (text files), wall street journal, can you give some suggestions, please?

Thanks a lot!

any word is truly appreciated!

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 20 October 2011 07:42 PM   [ Ignore ]   [ # 3 ]
Newbie
Rank
Total Posts:  4
Joined  2011-10-20

Hi,
I would say the benefit is from being able to manage your data more consistently. Excel basically doesn’t recognize the concept of a record that is fixed. For example, you can sort a subset of columns in a spreadsheet and destroy the integrity of the data whereas a database will maintain the record no matter how you sort.

Also, you can waste a lot of space in spreadsheets with redundant data whereas in a database that is properly constructed, you can more efficiently store and retrieve data.

And you can simplify backup procedures such that you backup the whole database instead of having to back up multiple files (spreadsheet or even SAS datasets).

But Yoost is probably a much better person to weigh in on the value of databases based on his experience.

Profile
 
 
Posted: 20 October 2011 08:12 PM   [ Ignore ]   [ # 4 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Thanks a lot for the information!

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 22 October 2011 02:17 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

If I can add that I think Excel is an excellent piece of software, it is only practical with small datasets (<65000 obs).
The best thing is the graphical user interface (you can see what is happening), which is helpful for debugging, etc, but not if something needs to be done more than once. (like re-running analyses on a different dataset).

best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 22 October 2011 10:24 AM   [ Ignore ]   [ # 6 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Hi Joost,

Thanks for what you added here.

I agree. older version of excel (xls) can handle even less obs comparing with the new versions (xlsx).

It seems that long term speaking, I should use some kind of data management system, like SQL, SQL lite ect. in case to handle large amount data, and for better data security.

Thank both of you!

 Signature 

Zenghui
A humble student of business

Profile
 
 
   
 
 
     SAS Macro - Winsorize ››