Keep consecutive observations only

[Costasg]

Hello,

I would like to keep stocks (ticker) that have at least 2 consecutive observations only. However, it could be the case that they have 1 observation in one quarter but 2 consecutive observations after 2 years. Or 2 consecutive observations at the beginning but single observations then after. In that case this stock should be kept (even the single observations). So, what I would like to do is delete the stocks that have only single quarter observations.

Any ideas?

[TomKari]

Is this going the direction you want?

data stocks;
input ticker $;
cards;
AA
AA
AB
AC
AD
AD
AE
run;

data wanted_stocks;
set stocks;

if ticker = lag(ticker) then
  output;
run;

proc sql;
create table unique_stocks as
  select distinct ticker
   from wanted_stocks;
create table final_result as
  select s.*
   from unique_stocks u inner join stocks s on(u.ticker = s.ticker);
quit;

[Costasg]

Hi Tom,

Thanks for the answer. I think that in the first step using the LAG, it omits the first observation of each stock. For example stock 1980-4 520 AA.

[Patrick]

Is this what you're after?

proc sql;

  create table want as

  select l.*

  from

    example l,

    (

      select  distinct ticker

      from example

      group by ticker,date

      having count(*)>1

    ) r

  where l.ticker=r.ticker

  ;

quit;

[TomKari]

Yes, but that's only to get the candidate list of tickers that have at least two consecutive. The next two steps reduce it to a distinct list of tickers, and then pulls out the full matching set from the original dataset.

Tom

[Tom]

I don't get the meaning of "2 consecutive observations" for this data.  Does that mean the TICKER appears for ANY manager for adjacent quarters? Or only for adjacent quarters for the same manager.  What about "TYPE"?  Once you have identified the TICKER values to keep use the list to get all the values.

Here is a method using DOW loops, but it will depend on your definition and your data sort order.

data want ;

do until (last.ticker) ;

   set have ;

   by ticker date;

   if last.date and 1 = intck('qtr',prev_date,date) then keepit=1;

   if last.date then prev_date=date;

end;

do until (last.ticker) ;

  set have ;

  by ticker date;

  if keepit then output;

end;

run;

[Costasg]

Tom,

What I mean is that the TICKER should have 2 consecutive quarters by ANY manager. Keep in mind that it could be the case that in the first quarters the stock could be traded only in single quarters but later on in consecutive quarters. In that case I want to keep the single quarters as well.

The code doesn't work well as at the final table includes tickers with a single quarter observation. For example, ADSC, AMTD, etc.

TomKari,

Again, somewhere it doesn't work well as at the final table includes tickers with a single quarter observation. For example, ADSC, AMTD, etc.

[Tom]

Identify the stocks to include. Then use that information to pull the information from the file.  That is what Pierre Gagnon posed below as two SQL steps and what I posted above as a single DATA step.

Or are you trying to say that stocks that only appear in the last quarter in the input data should also be kept?

[Patrick]

What about the SQL I've posted earlier? It first creates a list of distinct tickers with at least 2 rows in the same quarter and then matches these tickers to the full table so getting all rows from the source table with matching tickers.

Isn't this what you've asked for?

[PGStats · Accepted solution]

Maybe not the most efficient, but does the job :

proc sql;

create table tickers as

select distinct A.ticker

from

     sasforum.example as A inner join

     sasforum.example as B on A.ticker=B.ticker and

          intnx("QTR", A.date, 0) = intnx("QTR", B.date, 1);

create table conseq as

select *

from sasforum.example

where ticker in (select ticker from tickers)

order by ticker, date;

drop table tickers;

quit;

PG

[Costasg]

Thank you PGStats, it works! As you said not the most efficient, but it does the job.

Thank you Patrick, I need the ticker to have at least 2 quarters in a row, not at 2 rows in the same quarter.

Tom, your code included stocks that were traded in only 1 quarter. The stocks that I want are the ones that are traded in at least 2 quarters in a row (by any manager).

[Tom]

The data step will set the KEEPIT flag when it detects that the current qtr is right after the previous one.  There is no way it could select "singles", unless you have some other criteria I have missed that implies a different meaning for that (such requiring that the consecutive qtrs are by the same trader).

It identifies the same set of ticker symbols and same set of selected events as the SQL query. 

[PGStats]

I agree with Tom. Something must have gone wrong in your test of his code. When I try it on properly sorted data, it gives me the same number of observations as mine (398856) and the same set of tickers.

PG