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.
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;
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;
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.
Same basic technique. retain the cum_return, set it to return at first., and add return to it otherwise.
Not working. You will be appreciated if you write the code. Thanks.
If you need a consultant to do your work for you, hire one.
Otherwise show the code you tried, and the log.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.