create dataset with begdate and enddate

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

create dataset with begdate and enddate

[ Edited ]

Hi everyone,

 

I have a dataset like this (sample data). This dataset is organized by permno (stock identification), last date, and date. Variable "date" are the dates within the 2-year window from last date. For example, for the last date=31/7/2001, dates are ranging from 31/7/1999 to 31/7/2001. Symbol are stock symbols for each date

 

PermnoLast dateDateSYMBOL
1234531/07/200131/07/1999A
 12345 31/07/200101/08/1999A
 12345 31/07/200102/08/1999A
 12345 31/07/2001…………A
 12345 31/07/200115/02/2000A
 12345 31/07/200116/02/2000A1
 12345 31/07/2001………A1
 12345 31/07/200121/04/2001A1
 12345 31/07/200122/04/2001A2
 12345 31/07/2001……….A2
 12345 31/07/200126/06/2001A2
 12345 31/07/2001…………
1235615/09/2009……….B

 

Now I would like a dataset that summarizes the begining dates and enddates of a stock symbol. The output should look like this.

It shows that permno 12345 has a symbol = A from 31/7/1999 to 15/2/2000, and A1 from 16/2/2000 to 21/4/2001 and so on

 

PermnoLast dateBegdatesEnddatesSYMBOL
1234531/07/200131/07/199915/02/2000A
 12345 31/07/200116/02/200021/04/2001A1
 12345 31/07/200122/04/200126/06/2001A2
12356 15/09/2009…………………………

 

 

Could anyone show me how to do it? Thank you very much


Accepted Solutions
Solution
‎02-24-2018 07:34 AM
Valued Guide
Posts: 557

Re: create dataset with begdate and enddate

Posted in reply to trungcva112

Maybe adding "notsorted" option to the by-statement does the trick.

View solution in original post


All Replies
Contributor
Posts: 44

Re: create dataset with begdate and enddate

Posted in reply to trungcva112

Anyone has some ideas?

Valued Guide
Posts: 557

Re: create dataset with begdate and enddate

Posted in reply to trungcva112

Post some example data as data-step, so that we have something to work with.

 

That is untested code, assuming that the source-dataset is sorted by permno and symbol:

data work.want;
   set work.source;
   by permno symbol;

   length BegDate EndDate 8;
   retain BegDate;

   /*assuming taht Date is a sas date */
   format BegDate Enddate ddmmyys10.;

   if first.symbol then do;
      BegDate = Date;
   end;

   if last.symbol then do;
      EndDate = Date;
      output;
   end;

   drop date;
run;

 

Contributor
Posts: 44

Re: create dataset with begdate and enddate

Posted in reply to andreas_lds

Thanks. But different permno could have the same symbol. For example, the first observation of permno 12356 could be the same as the last observation of permno 12345. So how to deal with it?

Valued Guide
Posts: 557

Re: create dataset with begdate and enddate

Posted in reply to trungcva112

Sorry, don't get it. Can you post example data?

Super User
Posts: 6,751

Re: create dataset with begdate and enddate

Posted in reply to trungcva112

Have you already destroyed the data by blanking out most values for PERMNO and LAST_DATE, or do you have values for those on every observation?

Contributor
Posts: 44

Re: create dataset with begdate and enddate

[ Edited ]
Posted in reply to Astounding

Hi. I posted a new sample data for clarification. Hope it clarifies now

Valued Guide
Posts: 557

Re: create dataset with begdate and enddate

Posted in reply to trungcva112

And again the interesting part is blanked out.

Contributor
Posts: 44

Re: create dataset with begdate and enddate

Posted in reply to andreas_lds

Hi. So the data is like this

PermnoLast dateDateSYMBOL
1234531/07/200131/07/1999A
 12345 31/07/200101/08/1999A
 12345 31/07/200102/08/1999A
 12345 31/07/200103/08/1999Missing
 12345 31/07/200115/02/2000Missing
 12345 31/07/200116/02/2000A1
 12345 31/07/200117/02/2000A1
 12345 31/07/200121/04/2001A1
 12345 31/07/200122/04/2001A2
 12345 31/07/200123/04/2001A2
 12345 31/07/200126/06/2001Missing
 12345 31/07/200127/06/2001Missing
 12345 31/07/200228/06/2001A2
 12345 31/07/200231/07/2001A2
9851315/09/200915/09/2007A2
9851316/09/200916-09-07A2
9851317/09/200921-05-08B
9851318/09/200922-05-08B
9851319/09/200915-09-09B

 

Notice that there are 2 features that make it more difficult (I highlight above):

- There are random observations with missing symbols

- 2 different permnos could have the same symbol.

 

So when I use the code with first.symbol (I have to sort data with permno, symbol). The output is like this:

PermnoBegdateEnddateSymbol
1234531/07/199902/08/1999A
1234503/08/199927/06/2001Missing
1234528/06/200115/09/2007A2

 

Which is not right, because Permno 12345 does not have missing symbol all the way from 3/8/1999 to 27/6/2001. And it could not differentiate symbol A2 of permno 12345 with A2 of 98513.

 

The output should look like this

PermnoBegdateEnddateSymbol
1234531/07/199902/08/1999A
1234503/08/199915/02/2000Missing
1234516-02-0021-04-01A1
1234522-04-0123-04-01A2
1234526-06-0127-06-01Missing
1234528-06-0131-07-01A2
9851315-09-0716-09-07A2
9851321-05-0815-09-09B
Contributor
Posts: 44

Re: create dataset with begdate and enddate

Posted in reply to trungcva112
Anyone has some ideas?
Valued Guide
Posts: 557

Re: create dataset with begdate and enddate

Posted in reply to trungcva112

Providing data as data-step makes it easier to work on a solution. I don't think anybody is so bored that she/he enjoys copying a table and making the necessary changes so that it can be used as dataset.

Solution
‎02-24-2018 07:34 AM
Valued Guide
Posts: 557

Re: create dataset with begdate and enddate

Posted in reply to trungcva112

Maybe adding "notsorted" option to the by-statement does the trick.

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 220 views
  • 0 likes
  • 3 in conversation