Keep all observation in the first month/day

[AbuChowdhury]

Hi,

My dataset has data second by second observations. For example:

Firm           Year         Month         Day         Hour        Minute          Second

abc             2010           1                1              9               30                0

abc             2010           1                 1             9               30                1

:

:

abc            2012            12               31           16              0                 0

:

:

xyz              2008            4                6             11             30               1

:

:

xyz               2012          11                5            15              40              10

I would like to keep all observations in the first month (or in the first day) of a firm. Any suggestions will be appreciated.

[Patrick · Accepted solution]

@AbuChowdhury

First thing I'd do is to create a SAS Datetime value out of all these variables. Once done, sort the data by firm and datetime, retain the very first record and then use a SAS calendar function to determine which dates are within the selection range.

data have;
  input Firm $ Year Month Day Hour Minute Second;
  datalines;
abc 2010 1 1 9 30 0
abc 2010 1 1 9 30 1
abc 2012 12 31 16 0 0
;
run;

data inter;
  set have;
  format record_dttm datetime21.;
  record_dttm=dhms(mdy(month,day,year),hour,minute,second);
  drop Year Month Day Hour Minute Second;
run;

proc sort data=inter out=want;
  by Firm record_dttm;
run;

data want(drop=_:);
  set want;
  by firm record_dttm;

  if first.firm then _r_record_dttm=record_dttm;
  retain _r_record_dttm;

  if intck('dtday',_r_record_dttm,record_dttm)=0;
/*  if intck('dtweek',_r_record_dttm,record_dttm)=0;*/
/*  if intck('dtmonth',_r_record_dttm,record_dttm)=0;*/

run;

[AbuChowdhury]

Thank you. Your code works. Using your code, I get the observations for each firm in the first day. Now I have return data for each observation. I would like cumulate those returns by time for each firm. Fir example:

Firm         Return       record_dttm

abc           0.2            05FEB2004:10:55:00

abc           0.1            05FEB2004:10:56:00

:

:

xyz            0.15          07SEP2005:11:05:00

So there will be a new variable of cumulative return. For firm abc, first cumulative return will be 0.2, second cumulative return will be 0.3 and so on.

[Kurt_Bremser]

Same basic technique. retain the cum_return, set it to return at first., and add return to it otherwise.

[AbuChowdhury]

Not working. You will be appreciated if you write the code. Thanks.

[Kurt_Bremser]

If you need a consultant to do your work for you, hire one.

Otherwise show the code you tried, and the log.