topic Re: Keep all observation in the first month/day in SAS Data Management

Keep all observation in the first month/day

AbuChowdhury — Mon, 28 May 2018 09:08:14 GMT

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.

Re: Keep all observation in the first month/day

Patrick — Mon, 28 May 2018 10:15:29 GMT

@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;

Re: Keep all observation in the first month/day

AbuChowdhury — Mon, 28 May 2018 16:50:02 GMT

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.

Re: Keep all observation in the first month/day

Kurt_Bremser — Mon, 28 May 2018 18:27:27 GMT

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

Re: Keep all observation in the first month/day

AbuChowdhury — Mon, 28 May 2018 18:41:20 GMT

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

Re: Keep all observation in the first month/day

Kurt_Bremser — Mon, 28 May 2018 19:05:17 GMT

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

Otherwise show the code you tried, and the log.