topic Re: How to delete a variable when it has constant values for a certain period? in SAS Studio

How to delete a variable when it has constant values for a certain period?

Annalena — Tue, 28 Nov 2017 13:51:57 GMT

Hello everybody,

I am doing an event study with trading data using SAS Studio and I need to delete any stocks from my data set which have not been traded, i.e. their closing prices do not change over time. I have a sample of stocks with about 150 trading day records. My (for this task relevant) variables are date, firm and price. So what I want to do is to delete any firms which have not been traded for 3 days in a row or more. I attached one of my data sets. Does anybody know how to do this?

Thanks!

Re: How to delete a variable when it has constant values for a certain period?

Kurt_Bremser — Tue, 28 Nov 2017 14:01:19 GMT

Do you have a target date, eg "has not been traded in the last three days immediately before ...", or does any sequence of three days without a change qualify for deletion?

Re: How to delete a variable when it has constant values for a certain period?

Annalena — Tue, 28 Nov 2017 14:04:36 GMT

My estimation window is from April 1 to November 8, so any series of three days or more of no trading before November 8, 2016 qualifies for deletion.

Re: How to delete a variable when it has constant values for a certain period?

Kurt_Bremser — Tue, 28 Nov 2017 14:15:00 GMT

Use by group processing and the lag functions within a data step to determine firms to delete, then merge:

data del (keep=firm);
set sascomm.aerospacedefense;
by firm;
retain flag;
plag1 = lag(price);
plag2 = lag2(price);
if first.firm then flag = 0;
if lag(firm) = lag2(firm) = firm and plag1 = plag2 = price then flag = 1;
if last.firm and flag then output;
run;

data want;
merge
  sascomm.aerospacedefense
  del (in=d)
;
by firm;
if not d;
run;