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!
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;
Suggestion:
select * from TWX(rename=(dailyreturns=dailyreturns_TWX))
and
select * from GSPC(rename=(dailyreturns=dailyreturns_GSPC))
Do I replace what I have with your answer?
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?
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.
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;
Yes, that's what you want: columns date, TWX_returns, and GSPC_returns, where date applies to both TWX and GSPC.
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;
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
You are asking for a separate regression for each date. That's likely not what you want. Remove the by date statement.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.