Help using Base SAS procedures

Keep consecutive observations only

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

Keep consecutive observations only

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?


Accepted Solutions
Solution
‎05-26-2013 04:25 PM
Respected Advisor
Posts: 4,923

Re: Keep consecutive observations only

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

PG

View solution in original post


All Replies
PROC Star
Posts: 1,167

Re: Keep consecutive observations only

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;

Frequent Contributor
Posts: 75

Re: Keep consecutive observations only

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.

Respected Advisor
Posts: 4,173

Re: Keep consecutive observations only

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;

PROC Star
Posts: 1,167

Re: Keep consecutive observations only

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

Super User
Super User
Posts: 7,050

Re: Keep consecutive observations only

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;

Frequent Contributor
Posts: 75

Re: Keep consecutive observations only

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.

Super User
Super User
Posts: 7,050

Re: Keep consecutive observations only

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?

Respected Advisor
Posts: 4,173

Re: Keep consecutive observations only

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?

Solution
‎05-26-2013 04:25 PM
Respected Advisor
Posts: 4,923

Re: Keep consecutive observations only

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

PG
Frequent Contributor
Posts: 75

Re: Keep consecutive observations only

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).

Super User
Super User
Posts: 7,050

Re: Keep consecutive observations only

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. 

Respected Advisor
Posts: 4,923

Re: Keep consecutive observations only

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

PG
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 707 views
  • 3 likes
  • 5 in conversation