Calculating percent change using panel data set

[Virginia Razo]

I have a panel data set and I need to compute the percent change in a continuous variable for a 4 year period.  How can I compute this in SPSS?  

My data looks like this

Hospital 1          2008        unemployment rate
Hospital 1          2009        unemployment rate
Hospital 1          2010        unemployment rate
hospital 1          2011        unemployment rate
hospital 2          2008        ""
hospital 2          2009         ""
etc.

Can anyone assist me with this type of calculation using a panel data set?

Thanks,
Ginny Razo

[Jennifer Day-2]

Hi Out There!

I have a beautiful balanced panel dataset from 1998 to 2011, of election
results (incumbents re-elected, party turnover, voter turnout, etc.) for
municipalities in the USA, with no missing values.  I have matched 25
treatment cities to 25 control cities. All of the treatment cities
experienced a certain policy shock at different times between 2007 and
2011; the control cities are similar in demographic composition and
region, but did not experience the shock.  I also have relevant control
variables from the Census data.

In summary, I have a setup for a matched-pairs, treatment-control,
interrupted panel study.  I want to use a panel method to estimate the
effect of that shock.  I am writing to this list because I am not sure
what statistical method is suitable for this type of study.

Does anyone have any suggestions?

Thanks!!
Jennie

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Art Kendall]

open a new instance of SPSS. paste the syntax below int a syntax window. Run it.
is this what you are looking for?
data list list /hospital (f1) year(f4) unemployed (f4).
begin data.
1 2008 1000
1 2009 1010
1 2010 1020
1 2011 1031
2 2008 1000
2 2009  990
2 2010  980
2 2011  970
3 2008 1000
3 2010 1020
3 2011 1031
4 2008 1000
4 2009 1010
4 2010 1020
4 2011 1031
5 2008  500
5 2009  525
5 2010  552
5 2011  580
6 2008 100
6 2009  90
6 2010 110
6 2011 125
end data.
numeric demo1yrago since1yrago since2yrago since3yrago(pct7.1).
if lag(hospital,1) eq hospital demo1yrago = 100* ( ( (unemployed/lag(unemployed,1) ) -1) ).
do repeat periods = 1 to 3/ since= since1yrago to since3yrago.
do if hospital eq  lag(hospital,periods)  and year eq lag(year,periods)+periods.
compute  since = 100* ( ( ( (unemployed/lag(unemployed,periods) ) **(1/periods) ) )-1).
end if.
end repeat.
list.




On 5/31/2012 11:21 PM, Virginia Razo wrote:

I have a panel data set and I need to compute the percent change in a
continuous variable for a 4 year period.  How can I compute this in SPSS?

My data looks like this

Hospital 1          2008        unemployment rate
Hospital 1          2009        unemployment rate
Hospital 1          2010        unemployment rate
hospital 1          2011        unemployment rate
hospital 2          2008        ""
hospital 2          2009         ""
etc.

Can anyone assist me with this type of calculation using a panel data set?

Thanks,
Ginny Razo


--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Calculating-percent-change-using-panel-data-set-tp5713457.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Maguin, Eugene]

Jennie,

Your study seems to be in the political science area and I'd bet this is not the first time somebody has had a dataset like yours and has analyzed for policy shock effects. Step 1: find and read those articles.

Now, to give us something to work with state a specific hypothesis/variable that will show the effect and indicate whether you expect the effect to be a transient or a 'permanent' change in level.

Gene Maguin



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jenny Day
Sent: Friday, June 01, 2012 3:40 AM
To: [hidden email]
Subject: Matched Pairs, Treatment-Control, Interrupted Panel Study -- What Model will Work?

Hi Out There!

I have a beautiful balanced panel dataset from 1998 to 2011, of election results (incumbents re-elected, party turnover, voter turnout, etc.) for municipalities in the USA, with no missing values.  I have matched 25 treatment cities to 25 control cities. All of the treatment cities experienced a certain policy shock at different times between 2007 and 2011; the control cities are similar in demographic composition and region, but did not experience the shock.  I also have relevant control variables from the Census data.

In summary, I have a setup for a matched-pairs, treatment-control, interrupted panel study.  I want to use a panel method to estimate the effect of that shock.  I am writing to this list because I am not sure what statistical method is suitable for this type of study.

Does anyone have any suggestions?

Thanks!!
Jennie

=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Doug]

Google 'BACI'...Before-After-Control-Impact.

Gene makes a good point about transience of effects...experimentally,
this is similar to a "press" vs "pulse" experiment in ecology.

Doug Nutter

On Fri, Jun 1, 2012 at 11:47 AM, Maguin, Eugene <[hidden email]> wrote:

> Jennie,
>
> Your study seems to be in the political science area and I'd bet this is not the first time somebody has had a dataset like yours and has analyzed for policy shock effects. Step 1: find and read those articles.
>
> Now, to give us something to work with state a specific hypothesis/variable that will show the effect and indicate whether you expect the effect to be a transient or a 'permanent' change in level.
>
> Gene Maguin
>
>
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jenny Day
> Sent: Friday, June 01, 2012 3:40 AM
> To: [hidden email]
> Subject: Matched Pairs, Treatment-Control, Interrupted Panel Study -- What Model will Work?
>
> Hi Out There!
>
> I have a beautiful balanced panel dataset from 1998 to 2011, of election results (incumbents re-elected, party turnover, voter turnout, etc.) for municipalities in the USA, with no missing values.  I have matched 25 treatment cities to 25 control cities. All of the treatment cities experienced a certain policy shock at different times between 2007 and 2011; the control cities are similar in demographic composition and region, but did not experience the shock.  I also have relevant control variables from the Census data.
>
> In summary, I have a setup for a matched-pairs, treatment-control, interrupted panel study.  I want to use a panel method to estimate the effect of that shock.  I am writing to this list because I am not sure what statistical method is suitable for this type of study.
>
> Does anyone have any suggestions?
>
> Thanks!!
> Jennie
>
> =====================
> To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Virginia Razo]

Hello Art,
Thanks for taking your time to respond to my cry out for help.  The data I provided was just an example and I actually have more than 8000 observations.  How can I enter the begining and end of data list without typing every observations hospital by year?
Kind Regards,
Ginny
 

[Art Kendall]

if you already have the data in an SPSS file GET that file.  Then substitute your variable names for the names in my example syntax.
Begin data ... end data is used in the demo syntax because system files (*.sav) cannot be attached to post to the discussion list.
Also the demo variable won't be needed once you see that you get the same result for demo1yrago and since1yrago.
Recall that numerators and denominators need to be summed separately to get overall rates or percents.

Get file = . . .
numeric since1yrago since2yrago since3yrago(pct7.1).
do repeat periods = 1 to 3/ since= since1yrago to since3yrago.
do if hospital eq  lag(hospital,periods)  and year eq lag(year,periods)+periods.
compute  since = 100* ( ( ( (unemployed/lag(unemployed,periods) ) **(1/periods) ) )-1).
end if.
end repeat.
* calculate overall rates ASSUMING NO MISSING DATA.
sort cases by year.
dataset declare aggfile.
aggregate outfile=aggfile /break= year
 /sum_unemployed = sum(unemployed).
dataset activate aggfile.
numeric since1yrago since2yrago since3yrago(pct7.1).
do repeat periods = 1 to 3/ since= since1yrago to since3yrago.
compute  since = 100* ( ( ( (unemployed/lag(unemployed,periods) ) **(1/periods) ) )-1).
end repeat.
list.

_ if you have any missing data, medians would be meaningful as summary statistics, overall rates based on sums would not be meaningful.

On 6/2/2012 11:13 AM, Virginia Razo wrote:

Hello Art,
Thanks for taking your time to respond to my cry out for help.  The data I
provided was just an example and I actually have more than 8000
observations.  How can I enter the begining and end of data list without
typing every observations hospital by year?
Kind Regards,
Ginny


--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Calculating-percent-change-using-panel-data-set-tp5713457p5713488.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Ruben Geert van den Berg]

Dear Ginny,

I don't entirely see the point to this, but it certainly isn't a problem. Just run

new file.

input program.

loop id=1 to 8000.

leave id.

loop v2=2008 to 2011.

comp v1=rv.uni(0,100).

end case.

end loop.

end loop.

end file.

end input program.

exe.

variable labels id"Hospital number" v1"Unemployment rate"v2"Year".

formats v1(pct2.0)v2(f4.0).

Best,

Ruben

> Date: Sat, 2 Jun 2012 08:13:13 -0700

> From: [hidden email]
> Subject: Re: Calculating percent change using panel data set
> To: [hidden email]
>
> Hello Art,
> Thanks for taking your time to respond to my cry out for help. The data I
> provided was just an example and I actually have more than 8000
> observations. How can I enter the begining and end of data list without
> typing every observations hospital by year?
> Kind Regards,
> Ginny
>
>
> --
> View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Calculating-percent-change-using-panel-data-set-tp5713457p5713488.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD

[Art Kendall]

It is calculating the percent change for a one year period to the next (since1yrago) compounded over a 2 year period (since2yrago), and compounded over a 3 year period (since3yrago.
If that is the only variable you want  the COMPUTE for is "demo1yrago"  It is more intuitive IFF you are only interested in a one year change.
However, to find the percent change compounded over more than one year you need to use the formulae that take compounding into account.

for the demo syntax simply use the LIST command so it only lists the variables you are interested in.  Put an asterisk to the left of the one you are not interested in.
If you only want the 1 year change simply ignore the other columns in the data file.

In the aggregated file again simply ignore the other columns.  Remember the aggregated results are only meaningful if there is no missing data.
NOTE that in the simulated data Hospital #3 has no 2009 data record.  The actual aggregated numbers in the aggfile that would involve 2009 are invalid.
if you have no missing data the numbers in those positions would be valid. You can ignore the other columns if you wish.
2008    4600.00           
2009    3625.00    x     
2010    4702.00    29.7%    1.1%   
2011    4768.00    1.4%           x    1.2%

If I am misinterpreting your question, please explain in more detail. 
Is there missing data?
Are you interested in each hospital or only the aggregated data?

data list list /hospital (f1) year(f4) unemployed (f4).
begin data.
1 2008 1000
1 2009 1010
1 2010 1020
1 2011 1031
2 2008 1000
2 2009  990
2 2010  980
2 2011  970
3 2008 1000
3 2010 1020
3 2011 1031
4 2008 1000
4 2009 1010
4 2010 1020
4 2011 1031
5 2008  500
5 2009  525
5 2010  552
5 2011  580
6 2008 100
6 2009  90
6 2010 110
6 2011 125
end data.
numeric  since1yrago since2yrago since3yrago(pct7.1).
do repeat periods = 1 to 3/ since= since1yrago to since3yrago.
do if hospital eq  lag(hospital,periods)  and year eq lag(year,periods)+periods.
compute  since = 100* ( ( ( (unemployed/lag(unemployed,periods) ) **(1/periods) ) )-1).
end if.
end repeat.
list.
list /variables = hospital year since1yrago since2yrago since3yrago.
* calculate overall rates ASSUMING NO MISSING DATA.
*NOTE in the simulated data above Hospital #3 has NO 2009 data.
sort cases by year.
dataset declare aggfile.
aggregate outfile=aggfile /break= year
 /sum_unemployed = sum(unemployed).
dataset activate aggfile.
numeric since1yrago since2yrago since3yrago(pct7.1).
do repeat periods = 1 to 3/ since= since1yrago to since3yrago.
compute  since = 100* ( ( ( (sum_unemployed/lag(sum_unemployed,periods) ) **(1/periods) ) )-1).
end repeat.
list.
list  /variables = year  sum_unemployed since1yrago.






On 6/3/2012 4:24 PM, Ginny Razo wrote:

Hi Art,

 

Sorry to bother you again. After speaking with my chair, he has advised me to calculate the percent change by year. So for example, it would be more beneficial to calculate the percent change from 2005-2006 and from 2006-2007.  Is this what this is calculating?  If it is calculating the percent change from 2005 for 1 year, then 2 years than 3 years, I will need to modify it. 

 

Any assistance you can provide is greatly appreciated!

 

Kind Regards,

 

Ginny Razo

 

From: Art Kendall [[hidden email]]
Sent: Saturday, June 02, 2012 8:54 AM
To: Virginia Razo
Cc: [hidden email]
Subject: Re: [SPSSX-L] Calculating percent change using panel data set

 

if you already have the data in an SPSS file GET that file.  Then substitute your variable names for the names in my example syntax.
Begin data ... end data is used in the demo syntax because system files (*.sav) cannot be attached to post to the discussion list.
Also the demo variable won't be needed once you see that you get the same result for demo1yrago and since1yrago.
Recall that numerators and denominators need to be summed separately to get overall rates or percents.

Get file = . . .
numeric since1yrago since2yrago since3yrago(pct7.1).
do repeat periods = 1 to 3/ since= since1yrago to since3yrago.
do if hospital eq  lag(hospital,periods)  and year eq lag(year,periods)+periods.
compute  since = 100* ( ( ( (unemployed/lag(unemployed,periods) ) **(1/periods) ) )-1).
end if.
end repeat.
* calculate overall rates ASSUMING NO MISSING DATA.
sort cases by year.
dataset declare aggfile.
aggregate outfile=aggfile /break= year
 /sum_unemployed = sum(unemployed).
dataset activate aggfile.
numeric since1yrago since2yrago since3yrago(pct7.1).
do repeat periods = 1 to 3/ since= since1yrago to since3yrago.
compute  since = 100* ( ( ( (unemployed/lag(unemployed,periods) ) **(1/periods) ) )-1).
end repeat.
list.

_ if you have any missing data, medians would be meaningful as summary statistics, overall rates based on sums would not be meaningful.

Art Kendall

Social Research Consultants

On 6/2/2012 11:13 AM, Virginia Razo wrote:

Hello Art,

Thanks for taking your time to respond to my cry out for help.  The data I

provided was just an example and I actually have more than 8000

observations.  How can I enter the begining and end of data list without

typing every observations hospital by year?

Kind Regards,

Ginny

--

View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Calculating-percent-change-using-panel-data-set-tp5713457p5713488.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================

To manage your subscription to SPSSX-L, send a message to

[hidden email] (not to SPSSX-L), with no body text except the

command. To leave the list, send the command

SIGNOFF SPSSX-L

For a list of commands to manage subscriptions, send the command

INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD