topic Re: 3-year period for each firm in SAS Programming

3-year period for each firm

MarinaMag — Thu, 06 Dec 2018 05:33:57 GMT

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.

Re: 3-year period for each firm

mkeintz — Thu, 06 Dec 2018 05:50:38 GMT

Please show us some of your data in data step code.

Re: 3-year period for each firm

MarinaMag — Thu, 06 Dec 2018 06:00:26 GMT

Re: 3-year period for each firm

MarinaMag — Thu, 06 Dec 2018 06:02:25 GMT

Hello,

Here I have created:

date1=beg_fyr;

format date1 date9.;

date2= intnx('month', beg_fyr,36,'beg');

format date2 date9.;

Re: 3-year period for each firm

Kurt_Bremser — Thu, 06 Dec 2018 07:32:56 GMT

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.

Re: 3-year period for each firm

andreas_lds — Thu, 06 Dec 2018 07:13:16 GMT

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

Re: 3-year period for each firm

Kurt_Bremser — Thu, 06 Dec 2018 07:39:46 GMT

Just to show you what has already happened by using Excel:

Dates are stored as character in the Excel file; this also causes them to be character after import with EG
ret_crsp is mixed character/numeric in the Excel, so it ends up as character in the dataset; you probably had speciall "missing" values in the dataset

The macro would have prevented all that.

Bottom line: when working with SAS, forget that Excel even exists on your harddisk.

Re: 3-year period for each firm

mkeintz — Thu, 06 Dec 2018 07:44:48 GMT

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;

  if date>date2 then do;

Re: 3-year period for each firm

MarinaMag — Thu, 06 Dec 2018 07:50:20 GMT

Thank you very much!