Hi everyone,
I have a panel dataset below.
Now I have to compute a variable named "Lag mean sales". For example, the "lag mean sale" of firm 1 in the year 2002 = mean of sales over 4 quarters in the year 2001.
The tricky part is that I have to remove rows with missing sale data and rows with growth of sales is >75% or <-75%.
For example, the "lag mean sales" of firm 1 in the year 2002 = (10+20+21)/3 = 17
the "lag mean sales" of firm 1 in the year 2003 = (12+18)/2 = 15
firms | Year | Sales | Growth |
1 | QI/2001 | 10 | . |
1 | QII/2001 | 17.5 | 75% |
1 | QIII/2001 | 20 | 14% |
1 | QIV/2001 | 21 | 5% |
1 | QI/2002 | 12 | -43% |
1 | QII/2002 | 14 | 17% |
1 | QIII/2002 | . | . |
1 | QIV/2002 | . | . |
1 | QI/2003 | 11 | . |
1 | QII/2003 | 8 | -27% |
1 | QIII/2003 | 12 | 50% |
1 | QIV/2003 | 15 | 25% |
The output data is like this:
firms | Year | Sales | Growth | Lag mean sale |
1 | QI/2001 | 10 | . | |
1 | QII/2001 | 17.5 | 75% | |
1 | QIII/2001 | 20 | 14% | |
1 | QIV/2001 | 21 | 5% | |
1 | QI/2002 | 12 | -43% | 17 |
1 | QII/2002 | 14 | 17% | 17 |
1 | QIII/2002 | 16 | 14% | 17 |
1 | QIV/2002 | . | . | 17 |
1 | QI/2003 | 11 | . | 14 |
1 | QII/2003 | 8 | -27% | 14 |
1 | QIII/2003 | 12 | 50% | 14 |
1 | QIV/2003 | 15 | 25% | 14 |
Could someone show me how to do it, please? Any idea is much appreciated.
You would use two retained variables, perhaps something like:
data want; set have; retain lag_mean_sale no_elem; by year; if first.year then do; lag_mean_sale=sales; no_elem=1; end; else do; if sales ne . then do; lag_mean_sale=sum(lag_mean_sale); no_elem=sum(no_elem,1); end; end; run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.