More servicesWindows Live™
HomeHotmailSpacesOneCare
 
MSN
Sign in
 
 
Spaces home  Helping People SurviveProfileFriendsFilesMore Tools Explore the Spaces community

Blog
June 07

New SSRRN.com sitemap

Sitemap

Main Menu

11:29 PM | Add a comment | Send a message | Permalink | View trackbacks (0)
April 03

SAS Macro to Transpose Financial Data and Transform to STATA

 

Keywords: SAS Macro, SAS transpose, SAS to STATA

Cited from: http://www.survey-partner.co.uk/spsite/index.php/Details/SAS-Macro-to-Transpose-Financial-Data-v2.html

Category: PC1 SASPC1 Stata

Publisher's description

This version extends the first version,

1. Like the first version, it transpose accounting data into a Panel data format

2. It supports a sas export function that exports the accounting data back to excel format,

3. And it also provides a stata code that transform the excel format to the stata data format.

Enjoy!

User reviews

<< Start < Prev 1 Next > End >>

10

Author: Administrator

Date: 2008-04-02 22:46:45

Tested, and works for:
A. Transposing data
B. Export to excel format (*.CSV)
C. Transorm to Stata format (*.DTA)

5:26 PM | Add a comment | Send a message | Permalink | View trackbacks (0)
March 31

Transpose Thomson Financial Data

The download link is as follow:

http://www.survey-partner.co.uk/spsite/index.php/Details/10.html

or

http://www.ssrrn.com/index.php/Details/Transpose-Thomson-Financial-Data.html

 

This code can:

1. Import accounting data downloaded from Thomson Financial database using the excel addin.

2. Transpose the datasets to Panel Data format.

and then you can export them to other formats such as excel, stata, sas, etc.

/*---------------------------------------------------------------------------------------------------------------------
Current Version: 0.1
Please do not remove this information;
Originally Created by Helix Lee, Centre for Advanced Studies in Finance, Leeds University Business School, Leeds, UK. Email: buszl@leeds.ac.uk
You can modify the codes as you wish.

How to use it.

1. Make sure you copy them to the c:temp, or you can use other folder and please make sure you change all the c:test to you folder in this code


2. Download the data from Thomson One Financial database using the Excel Add-in, say you save it as accounting.xls;


3. Copy the header from the worksheet in the test.xls inside the zip file, replace the header information in the accounting.xls;

the current header information is:
Entity Name: code Currency: Y1999 Y2000 Y2001 Y2002 Y2003 Y2004 Y2005 Y2006
Please note that the current code suports year from 1999 to 2006 only, if you want to extend it, please add Y1998 etc. in the front, and Y2007 in the end. And add these information
in the %Transpose macro


4. Run the codes.
a. These two commands import and transpose them into the Panel Data format
%create_acc_data(var=Sales,data=test.Sales);
%create_acc_data(var=TotalAssets,data=test.TotalAssets);
b. Merge the two dataset;
%merge_datax(data1=test.TotalAssets,data2=test.Sales,sort_ids=code cyear,output=test.AccData);
c. Remove the raw datasets
%drop_table(tablename=test.Sales);
%drop_table(tablename=test.TotalAssets);
-----------------------------------------------------------------------------------------------------------------------*/

Codes:

libname test 'c:temp'; *Change the library name and this folder if you want;
run;
/*----------------------------------------------------------------------------------------------------------------------
Change the file name, if you excel file is accounting.xls, change the
DATAFILE= "c:temptest.xls"
TO
DATAFILE= "c:tempaccounting.xls"
-----------------------------------------------------------------------------------------------------------------------*/
%macro import_acc_data(var=,table=);
PROC IMPORT OUT= &table.
DATAFILE= "c:temptest.xls"
DBMS=EXCEL REPLACE;
SHEET=&var.;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
%mend ;
%macro sort_datax(data=,sort_ids=);
Proc sort data= &data.;
by &sort_ids.;
RUN;
%mend;
%macro merge_datax(data1=,data2=,sort_ids=,output=);
%sort_datax(data=&data1.,sort_ids=&sort_ids.);
%sort_datax(data=&data2.,sort_ids=&sort_ids.);
DATA &output.;
merge &data1. &data2.;
by &sort_ids.;
run;
%mend;
%macro drop_table(tablename=);
proc sql;
drop table &tablename.;
%mend;
%macro transpose_data(indata=,outdata=,var=,period=);
%if &period.=year %then %do;
%sort_datax(data=&indata.,sort_ids=code);
data &indata.;
set &indata.;
keep code Y1999 Y2000 Y2001 Y2002 Y2003 Y2004 Y2005 Y2006;
run;
proc transpose data=&indata.
out=work.TEMP1;
by code;
run;
data work.TEMP1;
set work.TEMP1;
*------------------------------------
You can extend the periods here;
-------------------------------------*
if _NAME_='Y1999' then cyear=year('01dec99'd);
if _NAME_='Y2000' then cyear=year('01dec00'd);
if _NAME_='Y2001' then cyear=year('01dec01'd);
if _NAME_='Y2002' then cyear=year('01dec02'd);
if _NAME_='Y2003' then cyear=year('01dec03'd);
if _NAME_='Y2004' then cyear=year('01dec04'd);
if _NAME_='Y2005' then cyear=year('01dec05'd);
if _NAME_='Y2006' then cyear=year('01dec06'd);
rename COL1=&var.;
RUN;
data &outdata.;
length code $10;
set work.TEMP1 (where=(code is not missing));
keep code cyear &var.;
run;
%drop_table(tablename=work.Temp1);
%end;
%mend;
%macro create_acc_data(var=,data=,sp500=);
%import_acc_data(var=&var.,table=work.Temp_var1);
%transpose_data(indata=work.Temp_var1,outdata=&data.,var=&var.,period=year);
%drop_table(tablename=work.Temp_var1);
%mend;
/* ----------------------------------------------------------------------------------------------------------
Example to run the codes
----------------------------------------------------------------------------------------------------------*/
%create_acc_data(var=Sales,data=test.Sales);
%create_acc_data(var=TotalAssets,data=test.TotalAssets);
%merge_datax(data1=test.TotalAssets,data2=test.Sales,sort_ids=code cyear,output=test.AccData);
%drop_table(tablename=test.Sales);
%drop_table(tablename=test.TotalAssets);

2:08 PM | Add a comment | Read comments (1) | Send a message | Permalink | View trackbacks (0)
View more entries
 

Files
An error occurred loading this module.

Blog list
No list items have been added yet.

Helping People Survive