No announcement yet.
  • Filter
  • Time
  • Show
Clear All
new posts

  • average change in variable

    I have data panel. I try to calculate the average change in variable "cashratio"
    1. Variable "SIC", consist of: construction, manufacturing and retail trade;
    2. Variable "Year" : the time range is from 2004 to 2014;

    1. How to calculate average change in cash ratios for all industries as a whole (SIC) and for each industry (construction, manufacturing and retail trade) over time (2004 to 2014)

    would appreciate any help
    many thanks
    Last edited by anzas rustamaji pratama; 23 Nov 2016, 11:08.

  • #2
    I have try this command in STATA but didn't work

    g AVCR=( cashratio [_n+1]- cashratio [_n])/ cashratio [_n]

    STATA said that "weights not allowed"


    • #3
      this the example of the dataset
      Year company name SIC cash ratio
      2004 ABC construction 0.2094
      -- ABC construction 0.2647
      2014 ABC construction 0.2635
      2004 FGH construction 0.2579
      -- FGH construction 0.2403
      2014 FGH construction 0.2631
      2004 KLM manufacturing 0.2671
      -- KLM manufacturing 0.2720
      2014 KLM manufacturing 0.2841
      2004 IUT manufacturing 0.2971
      -- IUT manufacturing 0.1507
      2014 IUT manufacturing 0.1456
      2004 WWW retail trade 0.1383
      -- WWW retail trade 0.1374
      2014 WWW retail trade 0.1383


      • #4
        Take out the spaces before the square brackets [


        • #5
          it's work, big thanks Nick.


          • #6
            but how to generate change in cash ratio by industry, because the command g AVCR=( cashratio [_n+1]- cashratio [_n])/ cashratio [_n] keep calculating previous company even their industry is different?


            • #7


              • #8
                is this command correct to prevent the calculation from different industry (SIC), e.g from previous table, company "KLM" in manufacturing, calculate its change in cash ratio from company "FGH" in construction

                by SIC:gen changeincr2=(cashratio[_n+1]-cashratio[_n])/cashratio[_n]


                • #9
                  I also have try this command:
                  bysort SIC(DataYearFiscal):gen changeincashratio=(cashratio-cashratio[_n-1])/(cashratio[_n-1]*(DataYearFiscal-DataYearFiscal[_n-1]))

                  but it come up with the result with a lot of missing value


                  • #10
                    another command that I have try:
                    bysort SIC(DataYearFiscal):gen changeincashratio=(cashratio-cashratio[_n-1])

                    but the there is still a lot of missing value in it.

                    i'm not really sure whether my command correct or not.

                    any help will be appreciated.


                    • #11
                      First of all, in the first observation in each SIC, you will, of course, have a missing result because there is no previous observation to provide a value for cashratio[_n-1]. Any other missing observations will arise from missing values of either the current value of cashratio or the preceding value of cashratio. So it seems you are surprised at the amount of missing values of cashratio in your data.

                      But you have a bigger problem. Looking at your example data, SIC and YEAR do not uniquely identify observations in your data, because there are multiple firms for each SIC and YEAR combination. So when you do -bysort SIC (YEAR)...-, Stata will sort the firms randomly within each SIC YEAR combination, which means that cashratio[_n-1] may refer to the cash ratio of a different firm! Moreover, if you rerun the same code, you will get different results each time.

                      There may also be another problem. Your calculations implicitly assume that when sorted by year, the _n-1 th observation always corresponds to the immediately preceding year. If your panels are complete, that is, have no gaps in the year variable, this will be true. But if there are any gaps, then the _n-1th observation may correspond to some earlier year, and your change will not reflect a one-year difference. To do this more safely, you should:

                      xtset firm year
                      gen relative_hange_cash_ratio = (cashratio - L1.cashratio)/L1.cashratio
                      This will guarantee that you are always subtracting the cash ratio from the same firm and the chronologically preceding year. If there is no information for the chronologically preceding year, you will get missing values.

                      I don't understand what you want when you ask for the average of this over SIC. Do you want a separate Industry-wide average for each year, or do you want a single industry-wide average that mashes together all of the individual years? Also, do you really want to average these? It doesn't seem very meaningful to do that.


                      • #12
                        Hay Clyde, thanks for the advice
                        I have run your command but it seems didn't work
                        xtset DataYearFiscal
                        panel variable: DataYearFiscal (unbalanced)
                        . gen relative_hange_cash_ratio=(cashratio-L1.cashratio)/L1.cashratio
                        time variable not set
                        I want to calculate the average change in cash ratio for:
                        a. whole industry (SIC) in each year (2004,2005,..,2014) and whole year (the ten year period 2004 to 2014) and;
                        b. each industry (construction, manufacturing and retail trade) in each year (2004,2005,..,2014) and whole year (the ten year period 2004 to 2014).

                        I run this command
                        table DataYearFiscal SIC, contents(mean changeincashratio ) row col center
                        Click image for larger version

Name:	average change in cash ratio.png
Views:	2
Size:	11.4 KB
ID:	1365457

                        Attached Files


                        • #13
                          I have run your command but it seems didn't work
                          No, actually, you didn't. My suggested command was:

                          xtset firm year
                          You didn't specify a firm variable in your -xtset- command. -xtset-'s first argument is always taken to be the panel variable. If a second argument is specified, that is taken to be the time variable. In programming, every detail is important--you have to be very meticulous in your work.

                          As for your averages, what is the difference between your a) and your b)? In your example data SIC and industry are the exact same variable. So a) and b) appear to be the same.

                          For averages across SIC within year:

                          by SIC year, sort: egen sic_year_avg_change = mean(relative_change_cash_ratio)
                          For an industry wide average over the whole ten year period just omit -year- from the above command (though I'm reluctant to tell you this because it is hard for me to see how the resulting average has any meaning.)


                          • #14
                            Thanks for the reply Clyde

                            I have re run the command, do I have to change the variable "CompanyName" into numeric? since it is a lot of company in my observation.

                            firm variable: CompanyName
                            Year variable: DataYearFiscal

                            xtset CompanyName DataYearFiscal
                            string variables not allowed in varlist;
                            CompanyName is a string variable

                            Honestly, I'm just doing my coursework and that question is part of it. I still figure out why I have to calculate it.


                            • #15
                              Yes, you need a numeric variable for the panel identifier in -xtset-. Probably the easiest way to do that is -encode CompanyName, gen(firm)-, which will create a numeric variable, firm, that corresponds to CompanyName. It will come with value labels attached, so it will look like the original string variable, but -xtset firm DataYearFiscal- will work.