BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Costasg
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

12 REPLIES 12
TomKari
Onyx | Level 15

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
Calcite | Level 5

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
Opal | Level 21

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
Onyx | Level 15

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Opal | Level 21

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
Opal | Level 21

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
Costasg
Calcite | Level 5

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
Super User Tom
Super User

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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