BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mng1
Fluorite | Level 6

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!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

10 REPLIES 10
PaigeMiller
Diamond | Level 26

Suggestion:

 

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

and

 

 

select * from GSPC(rename=(dailyreturns=dailyreturns_GSPC))
--
Paige Miller
mng1
Fluorite | Level 6

Do I replace what I have with your answer?

ballardw
Super User

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?

 

 

mng1
Fluorite | Level 6

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.

PGStats
Opal | Level 21

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
mng1
Fluorite | Level 6
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.
PGStats
Opal | Level 21

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

PG
mng1
Fluorite | Level 6

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;
mng1
Fluorite | Level 6

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

PGStats
Opal | Level 21

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

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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