DATA Step, Macro, Functions and more

Linear Regression with data in the same column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Linear Regression with data in the same column

[ Edited ]

Hi, I had two .csv files that i imported to SAS. My first work file is titled "TWX", second file is "GSPC". They both have the column "Daily Returns" and have their Ticker column as well (TWX and GSPC ticker names respectively), and I am trying to perform a regression of TWX daily returns against GSPC daily returns. So, I understand that I have to merge the files to perform the regression, so I typed in this:

 

proc sql;
     create table Portfolio as select
          * from TWX
                 outer union corr
     select * from GSPC
     order by ticker, date;
quit;

 

However, the Daily Returns from both TWX and GSPC were together in the same column called "Daily Return". They are still grouped by their tickers though, so for eg, TWX daily returns are on top and GSPC daily returns are at the bottom. 

 

So,

1) do i change the way i merge the files so that they are in two different daily return columns? If so, how do i do that?
2) If i dont have to change the way i merge the files, how do i perform a regression from the same column but TWX against GSPC?

 

Thank you!

 

 

 


Accepted Solutions
Solution
‎05-02-2017 04:00 AM
Respected Advisor
Posts: 4,644

Re: Linear Regression with data in the same column

[ Edited ]

You can only use dates for which both returns exist. So the proper query to prepare your regression is:

 

proc sql;
create table Portfolio as 
select
          a.date,
          a.returns as TWX_returns,
	  b.returns as GSPC_returns
from 
	TWX as a inner join
	GSPC as b on a.date=b.date;
quit;

 

PG

View solution in original post


All Replies
Trusted Advisor
Posts: 1,610

Re: Linear Regression with data in the same column

Suggestion:

 

select * from TWX(rename=(dailyreturns=dailyreturns_TWX))

and

 

 

select * from GSPC(rename=(dailyreturns=dailyreturns_GSPC))
Occasional Contributor
Posts: 6

Re: Linear Regression with data in the same column

Do I replace what I have with your answer?

Super User
Posts: 10,483

Re: Linear Regression with data in the same column

Can you show some example lines of data from each data set and what the desired merged/combined data would look like for those records?

 

 

Occasional Contributor
Posts: 6

Re: Linear Regression with data in the same column

This is what I have for TWX (excluding the part where i imported the data):

roc sort data = TWX;
		by date;
	format date worddate20.
			open dollar15.2
			high dollar15.2
			low dollar15.2  
			close dollar15.2
			volume comma15.;
run;

proc sql;
		alter table TWX 
		add Ticker char label = 'Stock Ticker Symbol';
		update TWX
				set Ticker = 'TWX';
quit; 
proc sql; 
	create table TWX as select
		*,
		(monotonic() - min(monotonic()) + 1) AS RowIndex label = 'Trading Day Index' 
	from TWX;
quit; 

proc sql; 
	create table TWX as select
		a.*, 
		b.close as LagClose,
		b.RowIndex as LagIndex
	from TWX as a left join TWX as b
	on a.Ticker = b.Ticker and (a.RowIndex - 1)  = (b.RowIndex); 
quit; 

proc sql; 
	create table TWX as select
	Ticker, 
	Date, 
	open,
	high,
	low,
	close,
	volume,
	LagClose, 
		(close - LagClose)/LagClose as return format = percentn8.3 label = 'Daily Return'
	from TWX;
quit; 

And I want my merged table to have 3 columns (Date, TWX Daily Returns, GSPC Daily Returns), so i can perform regression of TWX against GSPC.

Solution
‎05-02-2017 04:00 AM
Respected Advisor
Posts: 4,644

Re: Linear Regression with data in the same column

[ Edited ]

You can only use dates for which both returns exist. So the proper query to prepare your regression is:

 

proc sql;
create table Portfolio as 
select
          a.date,
          a.returns as TWX_returns,
	  b.returns as GSPC_returns
from 
	TWX as a inner join
	GSPC as b on a.date=b.date;
quit;

 

PG
Occasional Contributor
Posts: 6

Re: Linear Regression with data in the same column

What if both files have the same dates? I got the files from Yahoo Finance, where I downloaded their historical data after setting the same time period for both.
Respected Advisor
Posts: 4,644

Re: Linear Regression with data in the same column

Yes, that's what you want: columns date, TWX_returns, and GSPC_returns, where date applies to both TWX and GSPC.

PG
Occasional Contributor
Posts: 6

Re: Linear Regression with data in the same column

I have typed in the following, but the Portfolio still isn't created. 

proc sql; 
	create table GSPC as select
	Ticker, 
	Date, 
	open,
	high,
	low,
	close,
	volume,
	LagClose, 
		(close - LagClose)/LagClose as return format = percentn8.3 label = 'Daily Return'
	from GSPC;
quit;
proc sql;
	create table Portfolio as select
		a. date,
		a. return as TWX_returns,
		b. return as GSPC_returns,
	from
		TWX as a inner join
		GSPC as b on a.date=b.date;
quit;
Occasional Contributor
Posts: 6

Re: Linear Regression with data in the same column

Hi, the previous code you gave me worked in merging the tables horizontally. However, I couldn't perform a regression. This is what i have.

proc sql;
	create table Portfolio as select
		a.date,
		a.return as TWX_returns,
		b.return as GSPC_returns
	from
		TWX as a inner join
		GSPC as b on a.date = b.date;
quit;
proc reg data = Portfolio OUTEST = Final;
	model TWX_returns = GSPC_returns;
	BY date;
run;

I'm having problems with the proc reg part

Respected Advisor
Posts: 4,644

Re: Linear Regression with data in the same column

You are asking for a separate regression for each date. That's likely not what you want. Remove the by date statement.

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 138 views
  • 2 likes
  • 4 in conversation