topic Re: Keep consecutive observations only in SAS Procedures

Keep consecutive observations only

Costasg — Sat, 25 May 2013 20:27:38 GMT

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?

Re: Keep consecutive observations only

TomKari — Sat, 25 May 2013 21:15:46 GMT

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;

Re: Keep consecutive observations only

Costasg — Sat, 25 May 2013 21:45:05 GMT

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.

Re: Keep consecutive observations only

Patrick — Sun, 26 May 2013 01:01:28 GMT

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;

Re: Keep consecutive observations only

Tom — Sun, 26 May 2013 02:18:28 GMT

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;

Re: Keep consecutive observations only

TomKari — Sun, 26 May 2013 16:03:15 GMT

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

Re: Keep consecutive observations only

Costasg — Sun, 26 May 2013 17:09:35 GMT

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.

Re: Keep consecutive observations only

PGStats — Sun, 26 May 2013 20:25:39 GMT

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;

Re: Keep consecutive observations only

Tom — Sun, 26 May 2013 21:45:14 GMT

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?

Re: Keep consecutive observations only

Patrick — Sun, 26 May 2013 23:05:54 GMT

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?

Re: Keep consecutive observations only

Costasg — Sun, 26 May 2013 23:31:46 GMT

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

Re: Keep consecutive observations only

Tom — Mon, 27 May 2013 00:13:08 GMT

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.

Re: Keep consecutive observations only

PGStats — Mon, 27 May 2013 02:09:26 GMT

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.