Help using Base SAS procedures

How to calculate teh rolling standard deviations?

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How to calculate teh rolling standard deviations?

I have 30 years of profit data, and I want to calculate standard deviation for each year using past 5 years profits. The first year with standard deviation data should be the 6th year. ( I calculate the standard deviation for year 6 to year 30 ).

Please help.

Thanks.


Accepted Solutions
Solution
‎04-04-2013 10:42 PM
Respected Advisor
Posts: 4,173

Re: How to calculate teh rolling standard deviations?

Below code works under the assumption that you don't have missing years.

data want;
  array arr_prft {5} 8. _temporary_;
  set WORK.ROLLIMG_STANDARD_DEVIATION;
  by gvkey year;
  length std_prft 8;
  retain std_calc_flag 0;
  element+1;

  if element>dim(arr_prft) then
    do;
      element=1;
      std_calc_flag=1;
    end;

  arr_prft[element]=prft;

  if std_calc_flag then
    std_prft=std(of arr_prft

  • );
  •   output;

      if last.gvkey then
        call missing(of _all_);
    run;

    View solution in original post


    All Replies
    Respected Advisor
    Posts: 3,156

    Re: How to calculate teh rolling standard deviations?

    Since you don't have sample data, I can only give out general suggestions.

    1. Use Proc Expand. It has moving standard deviation built-in. it is the easiest way, but it requires license of SAS/ETS.

    2. Use Lagn(), in your case, Lag5().

    3. Use temporary Array(), presumably the most efficient one?

    Upon seeing your sample data, all of these approaches could be finalized.

    Haikuo

    Contributor
    Posts: 36

    Re: How to calculate teh rolling standard deviations?

    Hai.Guo

    I attached the simplified data,  One more thing, I need to do this by gvkey ( company id). I have thousands companies. Please help me with the code. I am not good at array function.

    Thanks

    Anna

    Attachment
    Solution
    ‎04-04-2013 10:42 PM
    Respected Advisor
    Posts: 4,173

    Re: How to calculate teh rolling standard deviations?

    Below code works under the assumption that you don't have missing years.

    data want;
      array arr_prft {5} 8. _temporary_;
      set WORK.ROLLIMG_STANDARD_DEVIATION;
      by gvkey year;
      length std_prft 8;
      retain std_calc_flag 0;
      element+1;

      if element>dim(arr_prft) then
        do;
          element=1;
          std_calc_flag=1;
        end;

      arr_prft[element]=prft;

      if std_calc_flag then
        std_prft=std(of arr_prft

  • );
  •   output;

      if last.gvkey then
        call missing(of _all_);
    run;

    Contributor
    Posts: 36

    Re: How to calculate teh rolling standard deviations?

    Patrick,

    Thanks. It is very likely I have missing years. If so, what kind of modifications should be?

    Anna

    Respected Advisor
    Posts: 4,173

    Re: How to calculate teh rolling standard deviations?

    As Hai.kuo pointed out SAS/ETS procedures like Proc Expand and Proc Timeseries would do the job for you.

    For the code I've posted easiest would be to fix up the time series adding years with missing prices.


    data All_Years(keep=gvkey year);
      set WORK.ROLLIMG_STANDARD_DEVIATION(keep=gvkey year);
      by gvkey year;
      lag_year=lag(year);
      source_year=year;
      if first.gvkey then output;
      else
      do year=lag_year+1 to source_year;
        output;
      end;
    run;


    data want;
      array arr_prft {5} 8. _temporary_;
      merge WORK.ROLLIMG_STANDARD_DEVIATION All_Years;
      by gvkey year;
      length std_prft 8;
      retain std_calc_flag 0;
      element+1;

      if element>dim(arr_prft) then
        do;
          element=1;
          std_calc_flag=1;
        end;

    if std_calc_flag then
        std_prft=std(of arr_prft

  • );
  •   arr_prft[element]=prft;

      output;

      if last.gvkey then
        call missing(of _all_);
    run;

    There was a logical error in my code. Please note that the calculation for the standard deviation must come BEFORE assigning the current prft to the array element.

    Contributor
    Posts: 38

    Re: How to calculate teh rolling standard deviations?

    Hi Patrick,

    I have a similar question but a little bit different Anna_Guo's. Could you also please help me to have a look?

    Thanks.

    Respected Advisor
    Posts: 4,173

    Re: How to calculate teh rolling standard deviations?

    Posted in reply to comeon2012

    I'm sure Ksharp's answer will solve your problem beautifully.

    Contributor
    Posts: 36

    Re: How to calculate teh rolling standard deviations?

    Patrick,

    Thanks. I think it is good enough for me.

    Anna

    🔒 This topic is solved and locked.

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

    Discussion stats
    • 8 replies
    • 868 views
    • 3 likes
    • 4 in conversation