Hello,
I have monthly data for multiple firms.
I have taken the begging of the fiscal year of each firm and add 36 months to create a time interval.
I want the data of each firm to be assigned in a specific 3-year period.
eg The data of a specific firm of 1989 1990 1991 must belong in date1=1989 and date2=1991.
The data of the same specific firm of 1992 must belong in date1=1992 and date2=1994 (and not in date1=1990 and and date2=1992).
Please note that I cannot just create periods for the entire sample, because the 3-year period is different for each firm, it is based on its fiscal year.
Does anyone can help me?
Thank you in advance.
This program is untested, but it could be easily tested if you take @Kurt_Bremser's code and applied it to all your sample data:
data want;
set have;
by permno;
retain date1 date2;
format date1 date2 date9. ;
if first.permno then do;
date1=begfyr;
date2=intnx('year',date1,3,'same');
end;
if date>=date2 then do;
date1=date2;
date2=intnx('year',date1,3,'same');
end;
run;
I notice your date2 is actually the first date of the next three-year period. If you prefer to make it the last date of the current 3-year period, then instead of;
date2=intnx('year',date1,3,'same');
use
date2=intnx('year',date1,3,'same')-1;
editted addition:
And also you would have to change
if date>=date2 then do;
to
if date>date2 then do;
Please show us some of your data in data step code.
Hello,
Here I have created:
date1=beg_fyr;
format date1 date9.;
date2= intnx('month', beg_fyr,36,'beg');
format date2 date9.;
This is what @mkeintz meant:
data have;
infile datalines dlm=';' dsd truncover;
input
PERMNO
DATE :date9.
COMNAM :$30.
ret_crsp :commax.
VWRETD :commax.
VWRETX :commax.
end_fyr :date9.
beg_fyr :date9.
year
month :commax.
mktrf :commax.
rf :commax.
risk_free :commax.
market_premium :commax.
ex_ret_mon :commax.
date1 :date9.
date2 :date9.
;
format date end_fyr beg_fyr date1 date2 date9.;
datalines4;
10000;31JAN1986;OPTIMUM MANUFACTURING INC;C;0,009829;0,008006;31OCT1986;01NOV1985;1986;1;0,65;0,56;0,0056;0,0065;;01NOV1985;01NOV1988
;;;;
run;
Excel files are useless for presenting SAS datasets, as they do not have fixed column attributes. Data steps with datalines enable you to exactly tell us how your dataset is structured and what content it has; we can recreate it by simply copying the code and submitting it.
No importing needed, which will in most cases give us a dataset different from the one you are using.
A macro that does the conversion of a dataset to a data step automatically is found in my second footnote.
Creating example/simulation data with a data step is an essential SAS skill which will always be useful in your SAS future.
@MarinaMag wrote:
Hello,
Here I have created:
date1=beg_fyr;
format date1 date9.;
date2= intnx('month', beg_fyr,36,'beg');
format date2 date9.;
Please provide the data as data-step using datalines statement. The dataset created when importing an excel-file may differ between platforms and SAS version. And, of course, because excel is a bad file-format for storing data.
Just to show you what has already happened by using Excel:
The macro would have prevented all that.
Bottom line: when working with SAS, forget that Excel even exists on your harddisk.
This program is untested, but it could be easily tested if you take @Kurt_Bremser's code and applied it to all your sample data:
data want;
set have;
by permno;
retain date1 date2;
format date1 date2 date9. ;
if first.permno then do;
date1=begfyr;
date2=intnx('year',date1,3,'same');
end;
if date>=date2 then do;
date1=date2;
date2=intnx('year',date1,3,'same');
end;
run;
I notice your date2 is actually the first date of the next three-year period. If you prefer to make it the last date of the current 3-year period, then instead of;
date2=intnx('year',date1,3,'same');
use
date2=intnx('year',date1,3,'same')-1;
editted addition:
And also you would have to change
if date>=date2 then do;
to
if date>date2 then do;
Thank you very much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.