topic Re: Industry sales Growth in SAS Programming

Industry sales Growth

abdulla — Wed, 07 Oct 2020 20:59:41 GMT

gvkey	fyear	ind	sale	Growth
1004	1980	1	214	.
1032	1980	1	214	.
1035	1980	1	214	.
1055	1981	1	332	1.551402
1070	1981	1	332	1.551402
1086	1981	1	332	1.551402
1101	1981	1	332	1.551402
1117	1982	1	445	1.340361
1132	1982	1	445	1.340361
1148	1982	1	445	1.340361
1005	1980	2	325	.
1022	1980	2	325	.
1027	1980	2	325	.
1040	1981	2	356	1.095385
1051	1981	2	356	1.095385
1062	1981	2	356	1.095385
1073	1981	2	332	1.095385
1084	1982	2	266	0.801205
1095	1982	2	266	0.801205
1106	1982	2	266	0.801205

I want to calculate industry sales growth. The last column is my expected result. sale/lag(sale). But I want it the way I have shown on the table. Can anyone help?

Re: Industry sales Growth

mklangley — Wed, 07 Oct 2020 20:40:57 GMT

@abdulla Is this what you're looking for?

data have;
    input gvkey fyear ind sale;
    datalines;
    1004 1980 1 214
    1032 1980 1 214
    1035 1980 1 214
    1055 1981 1 332
    1070 1981 1 332
    1086 1981 1 332
    1101 1981 1 332
    1117 1982 1 445
    1132 1982 1 445
    1148 1982 1 445
    ;
run;

data want;
    set have;
    retain growth 0;
    by sale;
    if first.sale
        then growth = sale / lag(sale);
run;

Result:

Re: Industry sales Growth

abdulla — Wed, 07 Oct 2020 20:47:23 GMT

Yes. But how about if I have more industries and sale is the same in any of the industries for two years? I have 48 industries and year is between 10980 and 1990. So, instead of first.sale I think I have to use first.fyear

Re: Industry sales Growth

mklangley — Wed, 07 Oct 2020 20:53:12 GMT

Yes, that would make sense, although it depends on what you are wanting to calculate: the growth since the last year, or the growth since the last sale amount, and whether it should be broken down by industry, etc. To figure those out, you could use some broader data (or introduce some more of those scenarios input your input dataset).

Re: Industry sales Growth

mklangley — Wed, 07 Oct 2020 20:59:31 GMT

@abdulla I just made up an example, which might capture what you brought up. Does this help?
(Note: I am assuming the input data is sorted by ind, then fyear.)

data have;
    input gvkey fyear ind sale;
    datalines;
    1004 1980 1 214
    1032 1980 1 214
    1035 1980 1 214
    1055 1981 1 332
    1070 1981 1 332
    1086 1981 1 332
    1101 1981 1 332
    1117 1982 1 445
    1132 1982 1 445
    1148 1982 1 445
    1234 1980 2 100
    2345 1980 2 100
    3456 1981 2 115
    4567 1981 2 115
    5678 1982 2 115
    6789 1982 2 115
    4321 1983 2 136
    5432 1983 2 136
    6543 1984 2 175
    7654 1984 2 175
    ;
run;

data want;
    set have;
    retain growth 0;
    by ind fyear;
    if first.fyear
        then growth = sale / lag(sale);
    if first.ind then growth = .;
run;