Help using Base SAS procedures

Copying down data in a panel

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

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.

 

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


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

Re: Copying down data in a panel

[ Edited ]
Posted in reply to finans_sas

 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

  

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Copying down data in a panel

Posted in reply to finans_sas

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;
Solution
‎08-03-2016 09:12 PM
Super User
Posts: 10,018

Re: Copying down data in a panel

[ Edited ]
Posted in reply to finans_sas

 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

  

Contributor
Posts: 57

Re: Copying down data in a panel

Thank you all for your help.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 327 views
  • 1 like
  • 3 in conversation