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

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

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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

View solution in original post

11 REPLIES 11
trungcva112
Obsidian | Level 7

Anyone has some ideas?

andreas_lds
Jade | Level 19

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;

 

trungcva112
Obsidian | Level 7

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?

andreas_lds
Jade | Level 19

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

Astounding
PROC Star

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?

trungcva112
Obsidian | Level 7

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

andreas_lds
Jade | Level 19

And again the interesting part is blanked out.

trungcva112
Obsidian | Level 7

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
trungcva112
Obsidian | Level 7
Anyone has some ideas?
andreas_lds
Jade | Level 19

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.

andreas_lds
Jade | Level 19

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 887 views
  • 0 likes
  • 3 in conversation