## Keep all observation in the first month/day

Solved
Regular Contributor
Posts: 194

# Keep all observation in the first month/day

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.

Accepted Solutions
Solution
4 weeks ago
Posts: 4,698

## Re: Keep all observation in the first month/day

[ Edited ]

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

All Replies
Solution
4 weeks ago
Posts: 4,698

## Re: Keep all observation in the first month/day

[ Edited ]

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

Regular Contributor
Posts: 194

## Re: Keep all observation in the first month/day

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.

Super User
Posts: 9,932

## Re: Keep all observation in the first month/day

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 194

## Re: Keep all observation in the first month/day

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

Super User
Posts: 9,932

## Re: Keep all observation in the first month/day

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

Otherwise show the code you tried, and the log.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.