topic Re: Copying down data in a panel in SAS Procedures

Copying down data in a panel

finans_sas — Tue, 02 Aug 2016 19:34:55 GMT

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.

State Year ElectionYear Votes

Alabama 2000 2000 100,000

Alabama 2001

Alabama 2002

Alabama 2003 2003 105,000

Alabama 2004

Alabama 2005

Alabama 2006 2006 112,000

Alaska 1990

Alaska 1991

Alaska 1992 1992 50,000

Alaska 1993

Alaska 1994

Alaska 1995

Alaska 1996 1996 52,000

Re: Copying down data in a panel

ballardw — Tue, 02 Aug 2016 20:09:20 GMT

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
   */
   if missing(votes) then votes=lastvotes;
   else lastvotes = votes;

   drop lastEyear lastvotes;
run;

Re: Copying down data in a panel

Ksharp — Thu, 04 Aug 2016 11:57:09 GMT

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
Alaska     1990   
Alaska     1991
Alaska     1992  1992   50,000
Alaska     1993   
Alaska     1994
Alaska     1995
Alaska     1996  1996   52,000
;
run;

data want;
    set have;
    by state;
    retain new_votes;
    if first.state then
        new_votes=.;
    if not missing(votes) then
        new_votes=votes;
run;

Result:

                          Election               new_
Obs     State     Year      Year       Votes     votes

  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

Re: Copying down data in a panel

finans_sas — Thu, 04 Aug 2016 01:13:16 GMT

Thank you all for your help.