SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Keep all observation in the first month/day

Accepted Solution Solved
Reply
Regular Contributor
Posts: 194
Accepted Solution

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
Respected Advisor
Posts: 4,698

Re: Keep all observation in the first month/day

[ Edited ]
Posted in reply to AbuChowdhury

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

 

View solution in original post


All Replies
Solution
4 weeks ago
Respected Advisor
Posts: 4,698

Re: Keep all observation in the first month/day

[ Edited ]
Posted in reply to AbuChowdhury

@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

Posted in reply to AbuChowdhury

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

Posted in reply to KurtBremser

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

Posted in reply to AbuChowdhury

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.

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

Discussion stats
  • 5 replies
  • 158 views
  • 0 likes
  • 3 in conversation