topic Re: Linear Regression with data in the same column in SAS Programming

Linear Regression with data in the same column

mng1 — Wed, 26 Apr 2017 18:11:18 GMT

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!

Re: Linear Regression with data in the same column

PaigeMiller — Wed, 26 Apr 2017 18:06:37 GMT

Suggestion:

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

and

select * from GSPC(rename=(dailyreturns=dailyreturns_GSPC))

Re: Linear Regression with data in the same column

ballardw — Wed, 26 Apr 2017 18:07:58 GMT

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?

Re: Linear Regression with data in the same column

mng1 — Wed, 26 Apr 2017 18:12:05 GMT

Do I replace what I have with your answer?

Re: Linear Regression with data in the same column

PGStats — Wed, 26 Apr 2017 19:20:25 GMT

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;

Re: Linear Regression with data in the same column

mng1 — Wed, 26 Apr 2017 19:22:33 GMT

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.

Re: Linear Regression with data in the same column

PGStats — Wed, 26 Apr 2017 19:27:47 GMT

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

Re: Linear Regression with data in the same column

mng1 — Wed, 26 Apr 2017 19:36:07 GMT

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;

Re: Linear Regression with data in the same column

mng1 — Wed, 26 Apr 2017 19:39:12 GMT

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.

Re: Linear Regression with data in the same column

mng1 — Wed, 26 Apr 2017 21:46:39 GMT

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

Re: Linear Regression with data in the same column

PGStats — Wed, 26 Apr 2017 22:09:02 GMT

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