topic Re: Buy minus Sell alternate column Variable in SAS Procedures

Buy minus Sell alternate column Variable

nochtan — Thu, 06 Aug 2015 01:05:55 GMT

Hi all,

I am trying to resolve an error I am facing where I cant get my BUY - SELL Variable. A sample of my table is below:

CUSIP	DATE	BUY	P2	P3	SELL
00036020	JUN06	.	.	.	.
00036020	NOV06	0.0076714433	.	.	.
00036020	JAN07	.	0.0075574196	.	.
00036020	FEB07	.	0.009285739	.	.
00036020	MAR07			0.004984601
00036020	APR07			0.0097122146
00036020	MAY07				0.0014465061
00036020	JUNE07				0.0004802898
00036020	JUL07	-0.011726471
00036020	SEP07				-0.015695027
01880210	JAN94	0.007576222

There are different CUSIP (companies) at different dates.

Since my BUY and Sell variables are of different columns and they are alternate (which means stock j has either buy or sell at only one time), when i try to do a BUY - SELL as BUY_SELL command, the BUY_SELL is variable empty.

What I am trying to achieve is a BUY - SELL variable, something like SUM(BUY) - SUM (SELL) = mean(difference) etc.

variable

SELL
P2
P3
P4
BUY

count (N)

55706

55952

56260

54811

56629

mean

0.000928724

0.0016922

0.0018418

0.0017465

0.0024443

std deviation

0.0135390

0.0133077

0.0132802

0.0130985

0.0134837

t value

16.19

30.08

32.90

31.22

43.14

<.0001

BUY - SELL VARIABLE

Re: Buy minus Sell alternate column Variable

EricHoogenboom — Thu, 06 Aug 2015 08:16:08 GMT

Hello Nochtan,

I do not fully understand your question, it seems a mix of sum and mean.

Maybe you can add a small want - have example dataset.

Proc means aggregates your data to the level of cusip:

proc means data=have noprint;

class cusip;

var buy p1 p2 sell;

output out=want mean=;

run;

Hope this helps,

Eric

Re: Buy minus Sell alternate column Variable

RW9 — Thu, 06 Aug 2015 08:54:39 GMT

Hi,

You haven't posted any test data in the form of a datastep, so I can't provide a working example, however something like:

proc sql;

create table WANT as

select CUSPID,

sum(BUY) as BUY,

sum(SELL) as SELL,

sum(BUY) - sum(SELL) as BUY_SELL

from HAVE

group by CUSPID;

quit;

Re: Buy minus Sell alternate column Variable

nochtan — Thu, 06 Aug 2015 09:22:14 GMT

Hi Eric, I have already figured out. But I have another question to ask to seek your advice.

Hi all,

I am using IBES analysts forecast revision and CRSP for stock prices.

I am quite new to SAS and I have been trying to figure out how to form portfolio of analysts forecast revision to examine stock price drift for 1month, 3months, 6months, 12months and 24months. What I am trying to achieve is to hold stocks at time t, and buy or sell stocks according to the degree of analysts' forecast revision (1 = lowest (SELL) and 5 = highest (BUY)) at time t, and find the cumulative returns for 1,3,6,12 and 24 months of the stocks bought or sold at time t. (Without changing the portfolio). In other words, stocks will stay in the portfolio at time t and I will buy or sell according to the ranking of analysts' forecast revision.

HOWEVER, I only can manage to find stock returns at time t to analysts' forecast revision portfolio at time t, and I cant get the codes for the cumulative returns.

Below are my codes:

/* Step 1. Specifying Options */

%let J=6; /* Formation Period Length: J can be between 3 to 12 months - can be adjusted */

%let K=6; /* Holding Period Length: K can be between 3 to 12 months - can be adjusted*/

%let begdate=01JAN1994;

%let enddate=31DEC2014;

run;

/* Step 2. Assign Ranks to the Next 6 (K) Months After Portfolio Formation */

/* Forecast_revision_portfolio is the portfolio rank variable taking values between 1 and 5: */

/* 1 - the lowest momentum group: Losers */

/* 5 - the highest momentum group: Winners */

data getr_2 ;

set getr_2;

HDATE1 = intnx("MONTH",date, 0,"B")-1;

HDATE2 = intnx("MONTH",date,&k-1,"E");

format HDATE1 HDATE2 monyy.;

label HDATE1= "First Holding Date";

label HDATE2= "Last Holding Date";

run;

/* Portfolio returns are average monthly returns rebalanced monthly */

proc sql;

create table getr_3

as select distinct*

from getr_2 as a, recency as b

where a.cusip=b.cusip

and a.HDATE1<=b.date<=a.HDATE2

order by cusip, date;

quit;

/* Step 4. Calculate Equally-Weighted Average Monthly Returns */

proc sort data=getr_3 nodupkey; by cusip date analys; run;

proc sort data=getr_3; by date forecast_revision_rank HDATE1;run;

/* Calculate Equally-Weighted returns across portfolio stocks */

/* Every date, each MOM group has J portfolios identified by formation date */

proc means data = getr_3 noprint;

by date forecast_revision_rank HDATE1;

var mean_returns;

output out = umd3 mean=mean_returns;

run;

/* Portfolio average monthly returns */

proc sort data=umd3; by date Forecast_revision_rank;

where year(date) >= year("&begdate"d);

run;

/* Create one return series per MOM group every month */

proc means data = umd3 noprint;

by date forecast_revision_rank;

var mean_returns;

output out = ewretdat mean= ewret std = ewretstd;

run;

proc sort data=ewretdat; by forecast_revision_rank ; run;

Title "Table 1: Returns of Analysts' Forecast Revision Portfolios";

Title2 "Portfolios based on 6 months lagged return and held for 6 months";

proc means data=ewretdat n mean t probt;

class Forecast_revision_rank;

var ewret;

run;

/* Step 5. Calculate Long-Short Portfolio Returns */

proc sort data=ewretdat; by date Forecast_revision_rank; run;

proc transpose data=ewretdat out=ewretdat2

(rename = (_1=SELL _2=PORT2 _3=PORT3 _4=PORT4 _5=BUY)

drop=_NAME_ _LABEL_);

by date;

id Forecast_revision_rank;

var ewret;

run;

/* Compute Long-Short Portfolio Cumulative Returns */

data ewretdat3;

set ewretdat2;

by date;

LONG_SHORT=BUY-SELL;

retain CUMRET_BUY CUMRET_SELL CUMRET_LONG_SHORT 0;

CUMRET_BUY = (CUMRET_BUY+1)*(BUY+1)-1;

CUMRET_LOSERS = (CUMRET_SELL +1)*(SELL +1)-1;

CUMRET_LONG_SHORT = (CUMRET_LONG_SHORT+1)*(LONG_SHORT+1)-1;

format BUY SELL LONG_SHORT PORT: CUMRET_: percentn12.1;

run;

proc means data=ewretdat3 n mean t probt;

var BUY SELL LONG_SHORT;

run;

Below is a screen shot of the table:

DATE	CUSIP	COMNAM	Adjusted_Price	high	days_since_52WH	Returns	mean_returns	RR	obsno	analys	fpedats	Forecast_Revision	forecast_revision_Rank	HDATE1	HDATE2
Jun-06	00036020	AAON INC	5.068641945	5.635555691	37	0.009441376	0.004199479	4	121980	112011	30-Jun-06	0.33408324	5	May-06	Nov-06
Jun-06	00036020	AAON INC	5.068641945	5.635555691	37	0.009441376	0.004199479	4	121980	79788	30-Jun-06	0.466816648	5	May-06	Nov-06
Nov-06	00036020	AAON INC	5.515061743	5.730370416	11	0.034074077	0.00757694	4	Oct-06	Apr-07
Jan-07	00036020	AAON INC	5.428148058	5.730370416	51	-0.006148285	0.002447311	3	Dec-06	Jun-07
Feb-07	00036020	AAON INC	5.485432189	5.827160494	7	0.000720737	0.000650488	4	Jan-07	Jul-07
May-07	00036020	AAON INC	5.868641794	5.929876634	13	-0.003020139	0.005742444	4	Apr-07	Oct-07
Jul-07	00036020	AAON INC	5.908148118	6.806913399	9	-0.009602679	-0.002715823	5	Jun-07	Dec-07
Aug-07	00036020	AAON INC	6.204444603	6.816789792	19	0.030004949	0.002654603	5	Jul-07	Jan-08
Oct-07	00036020	AAON INC	5.410370438	6.816789792	61	0.033389935	-0.003414025	3	Sep-07	Mar-08
Nov-07	00036020	AAON INC	5.638518722	6.816789792	82	0.004221632	0.002566941	3	Oct-07	Apr-08
Dec-07	00036020	AAON INC	5.872592502	6.816789792	102	-0.025086091	0.00248648	2	121980	107780	31-Dec-07	-0.320916905	1	Nov-07	May-08

Please help my guardian angels. Thank You.

Re: Buy minus Sell alternate column Variable

nochtan — Thu, 06 Aug 2015 09:32:39 GMT

Hi RW9,

The formula that you recommend will have a NULL Buy_Sell variable because all the stocks have alternate buy, P2, P3, P4, Sell variables. I have tried.

What I did was this:

proc means data = want4 noprint;

by date RR;

var roll_avg;

output out = want4 mean= ewret std = ewretstd;

run;

proc sort data=want4; by RR ; run;

Title "Recency Strategy (Table 1): Returns of Relative Strength Portfolios";

Title2 "Portfolios based on &J month lagged return and held for &K months";

proc means data=want4 n mean t probt;

class RR;

var ewret;

run;

/* Step 5. Calculate Long-Short Portfolio Returns */

proc sort data=want4; by date RR; run;

proc transpose data=want4 out=want5

(rename = (_1=RRL _2=PORT2 _3=PORT3 _4=PORT4 _5=RRH)

drop=_NAME_ _LABEL_);

by date;

id RR;

var ewret;

run;

/* Compute Long-Short Portfolio Cumulative Returns */

data Want5;

set want5;

by date;

LONG_SHORT=RRH-RRL;

retain CUMRET_RRH CUMRET_RRL CUMRET_LONG_SHORT 0;

CUMRET_RRH = (CUMRET_RRH+1)*(RRH+1)-1;

CUMRET_RRL = (CUMRET_RRL +1)*(RRL +1)-1;

CUMRET_LONG_SHORT = (CUMRET_LONG_SHORT+1)*(LONG_SHORT+1)-1;

format RRH RRL LONG_SHORT PORT: CUMRET_: percentn12.1;

run;

Sorry for the mess! 😕

Re: Buy minus Sell alternate column Variable

EricHoogenboom — Thu, 06 Aug 2015 11:47:31 GMT

Hello Nochtan,

I am very sorry but I am not a helpdesk.

Please shorten your question to the tiny part that you don't know yet.

Cheers,

Eric

Re: Buy minus Sell alternate column Variable

nochtan — Thu, 06 Aug 2015 11:57:23 GMT

Hi Eric,

baiscally this is the main part:

Step 2. Assign Ranks to the Next 6 (K) Months After Portfolio Formation */

/* Forecast_revision_portfolio is the portfolio rank variable taking values between 1 and 5: */

/* 1 - the lowest momentum group: Losers */

/* 5 - the highest momentum group: Winners */

data getr_2 ;

set getr_2;

HDATE1 = intnx("MONTH",date, 0,"B")-1;

HDATE2 = intnx("MONTH",date,&k-1,"E");

format HDATE1 HDATE2 monyy.;

label HDATE1= "First Holding Date";

label HDATE2= "Last Holding Date";

run;

where I can't seem to assign ranks to the next (1,3,6,12,24months) even after using the intnx function. I would like to hold stocks at time t and find the returns at the desired time t+?

Re: Buy minus Sell alternate column Variable

RW9 — Thu, 06 Aug 2015 13:28:18 GMT

Can you not then just coalesce the values, sorry am not really clear on what relation p2 or p3 is to the buy or sell column, so I will assume they relate to sell

proc sql;

create table WANT as

select CUSPID,

sum(BUY) as BUY,

sum(coalesce(P2,P3,SELL)) as SELL,

sum(BUY) - sum(coalesce(P2,P3,SELL) as BUY_SELL

from HAVE

group by CUSPID;

quit;

Another tip is to normalise your data, i.e. rather than having columns for each bit, have rows, and an identifier column:

CUSPID DATE ID RESULT

0011 11jan12 BUY 0.3453525

0011 11jan12 P2 0.45564

...

It makes it easier to do aggregates.