Solved
Contributor
Posts: 57

# Copying down data in a panel

Hello Everyone:

I have a particular event (election) that takes place at different intervals for different states (sometimes at different intervals for the same state). Below is a sample data. Even though the election does not take place every year, I have some other data that have values every year (such as population). I would like to be able to copy the election data (Votes) from the election year to the subsequent year(s) until the next election date. For example, at the end, Alabama should have 100,000 votes for 2001 and 2002, Alaska should have 50,000 votes for 1993, 1994, and 1995. Since there is no data for the election data preceding 1992 for Alaska, 1990 and 1991 should stay blank.

I used various statements involving lags, but could not get what I need. Thank you for your help in advance.

Alabama    2000      2000                       100,000

Alabama    2001

Alabama    2002

Alabama    2003       2003                     105,000

Alabama    2004

Alabama    2005

Alabama    2006       2006                      112,000

Accepted Solutions
Solution
‎08-03-2016 09:12 PM
Super User
Posts: 10,787

## Re: Copying down data in a panel

[ Edited ]

Is this what you are looking for ?

``````data have;
infile cards truncover expandtabs;
input State \$  Year  ElectionYear  Votes : comma10.;
cards;
Alabama    2000  2000   100,000
Alabama    2001
Alabama    2002
Alabama    2003  2003   105,000
Alabama    2004
Alabama    2005
Alabama    2006  2006   112,000
;
run;

data want;
set have;
by state;
if first.state then
run;``````

Result:

```                          Election               new_

1    Alabama    2000      2000      100000    100000
2    Alabama    2001         .           .    100000
3    Alabama    2002         .           .    100000
4    Alabama    2003      2003      105000    105000
5    Alabama    2004         .           .    105000
6    Alabama    2005         .           .    105000
7    Alabama    2006      2006      112000    112000
8    Alaska     1990         .           .         .
9    Alaska     1991         .           .         .
10    Alaska     1992      1992       50000     50000
11    Alaska     1993         .           .     50000
12    Alaska     1994         .           .     50000
13    Alaska     1995         .           .     50000
14    Alaska     1996      1996       52000     52000
```

All Replies
Super User
Posts: 13,583

## Re: Copying down data in a panel

Something like this perhaps. RETAIN is very useful for holding when the interval changes or may not be known as long as you only need one value at a time.

``````data want;
set have;
retain lastEyear lastvotes .;     /* if either election year or votes are character you
need set a length to hold the charcter values or
change the . to "" to idicate character variable*/
if missing(electionyear) then electionyear=lastEyear;
else lastEyear=electionyear;
/* if you NEVER EVER will have votes missing other than with
missing election year these could be in the same If/then above
*/

run;``````
Solution
‎08-03-2016 09:12 PM
Super User
Posts: 10,787

## Re: Copying down data in a panel

[ Edited ]

Is this what you are looking for ?

``````data have;
infile cards truncover expandtabs;
input State \$  Year  ElectionYear  Votes : comma10.;
cards;
Alabama    2000  2000   100,000
Alabama    2001
Alabama    2002
Alabama    2003  2003   105,000
Alabama    2004
Alabama    2005
Alabama    2006  2006   112,000
;
run;

data want;
set have;
by state;
if first.state then
run;``````

Result:

```                          Election               new_

1    Alabama    2000      2000      100000    100000
2    Alabama    2001         .           .    100000
3    Alabama    2002         .           .    100000
4    Alabama    2003      2003      105000    105000
5    Alabama    2004         .           .    105000
6    Alabama    2005         .           .    105000
7    Alabama    2006      2006      112000    112000
8    Alaska     1990         .           .         .
9    Alaska     1991         .           .         .
10    Alaska     1992      1992       50000     50000
11    Alaska     1993         .           .     50000
12    Alaska     1994         .           .     50000
13    Alaska     1995         .           .     50000
14    Alaska     1996      1996       52000     52000
```

Contributor
Posts: 57

## Re: Copying down data in a panel

Thank you all for your help.

☑ This topic is solved.