topic Re: How to fill in current year's variable with last year's variable value if the current year is bl in SAS Data Management

How to fill in current year's variable with last year's variable value if the current year is blank

r4321 — Thu, 18 Apr 2019 20:50:38 GMT

Hello all,

I could use your help with some coding.

I have data that I have organized into a panel type dataset. There are multiple organizations in the dataset that are observed each year. I also have a variable in this dataset that has a lot of missing values. For this variable, if it is missing in a given year, I would like to fill in the current year's value with the previous year's value.

However, the previous year's value may also be missing. It may even be missing for several years back when the firm first entered the dataset (e.g., firm enters dataset in 2001, but does not have an observed value on said variable until 2007), in which case I would want the values for 2001-2006 to be changed from missing to zeros. Additionally, a value could appear on the variable when it first enters the dataset, but then be missing for two subsequent years, the value could appear again, and then be missing for next year, and so on. So the missingness is random. To sum up, I would like my variable to take on the value of the last year in which it had a value. if it does not have a value in any of the previous years, I would like it to take on a zero up until it has a value (if ever). I have created a small example below.

Firm ID Year Variable Transformed to ---> Firm ID Year Variable

1 2002 . 1 2002 0

1 2003 . 1 2003 0

1 2004 1 1 2004 1

1 2005 . 1 2005 1

2 2002 2 2 2002 2

2 2003 . 2 2003 2

2 2004 3 2 2004 3

2 2005 . 2 2005 3

3 1996 . 3 1996 0

3 1997 . 3 1997 0

3 1998 . 3 1998 0

If I could get some guidance with how to code this, I would really appreciate it! To be honest, I'm not sure where to start

with this.

Thanks in advance.

Re: How to fill in current year's variable with last year's variable value if the current year is bl

novinosrin — Thu, 18 Apr 2019 20:55:33 GMT

hi @r4321 Very straightforward update

 
data have;
input FirmID   Year     Variable  ;          
cards;
1             2002        .                                                                  
1             2003        .                                                                    
1             2004        1                                                               
1             2005        .                                                                 
2             2002        2                                                                  
2             2003        .                                                                 
2             2004        3                                                                   
2             2005        .                                                            
3             1996        .                                                                  
3             1997        .                                                                   
3             1998        .                                                                   
;
 
data want;
update have(obs=0) have;
by firmid;
if missing(variable) then variable=0;
output;
run;

Re: How to fill in current year's variable with last year's variable value if the current year is bl

Reeza — Thu, 18 Apr 2019 20:56:59 GMT

data want;
set have;
by firmID;

retain new_var;

if first.firmID and missing(variable) then new_var = 0;
else if not missing(variable) then new_var = variable;

run;

Use RETAIN and FIRST logic here. I think above is all you need, but if I"m missing some conditions feel free to post back.

@r4321 wrote:

Hello all,

I could use your help with some coding.

I have data that I have organized into a panel type dataset. There are multiple organizations in the dataset that are observed each year. I also have a variable in this dataset that has a lot of missing values. For this variable, if it is missing in a given year, I would like to fill in the current year's value with the previous year's value.

However, the previous year's value may also be missing. It may even be missing for several years back when the firm first entered the dataset (e.g., firm enters dataset in 2001, but does not have an observed value on said variable until 2007), in which case I would want the values for 2001-2006 to be changed from missing to zeros. Additionally, a value could appear on the variable when it first enters the dataset, but then be missing for two subsequent years, the value could appear again, and then be missing for next year, and so on. So the missingness is random. To sum up, I would like my variable to take on the value of the last year in which it had a value. if it does not have a value in any of the previous years, I would like it to take on a zero up until it has a value (if ever). I have created a small example below.

Firm ID Year Variable Transformed to --->   Firm ID Year Variable

1 2002 .   1 2002 0

1 2003 .   1 2003 0

1 2004 1 1 2004 1

1 2005 . 1 2005 1

2 2002 2 2 2002 2

2 2003 .   2 2003 2

2 2004 3 2 2004 3

2 2005 . 2 2005 3

3 1996 .   3 1996 0

3 1997 .   3 1997 0

3 1998 .   3 1998 0

If I could get some guidance with how to code this, I would really appreciate it! To be honest, I'm not sure where to start

with this.

Thanks in advance.

Re: How to fill in current year's variable with last year's variable value if the current year is bl

r4321 — Fri, 19 Apr 2019 16:20:19 GMT

Hello all,

Thank you for your help so far and quick replies.

I have that the code you provided is effective at producing zeros if there are missing values... However, it does not carry over the previous years value into the next year if the previous year is not missing and the current year is missing. In the case that there is a value in a given year on the variable... I want it to carry over to subsequent years that are missing until there is again a value. See example data:

Firm ID Year Variable Transformed to ---> Firm ID Year Variable

1 2002 . 1 2002 0

1 2003 . 1 2003 0

1 2004 100 1 2004 100

1 2005 . 1 2005 100

1 2006 . 1 2006 100

1 2007 . 1 2007 100

2 2002 22 2 2002 22

2 2003 . 2 2003 22

2 2004 33 2 2004 33

2 2005 . 2 2005 33

2 2006 . 44 2 2006 44

3 1994 3 1994 0

3 1995 . 3 1995 0

3 1996 3 1996 0

3 1997 . 3 1997 0

3 1998 . 3 1998 0

I really appreciate your help in figuring this out!

Thanks again.

Re: How to fill in current year's variable with last year's variable value if the current year is bl

novinosrin — Fri, 19 Apr 2019 16:46:59 GMT

Hello @r4321 Have you really tested the code I gave you

data want;
update have(obs=0) have;
by firmid;
if missing(variable) then variable=0;
output;
run;

Re: How to fill in current year's variable with last year's variable value if the current year is bl

r4321 — Fri, 19 Apr 2019 17:41:06 GMT

Interesting ---

So I ran it again and it works.

I think the problem was, I had multiple variables in the dataset and I was comparing the new variable that had been created (with the values filled in) to the old variable it was being transformed from (with the missing values) and it was confusing me because the old variable was being transformed by the code as well despite not being specified in the code (not sure why this was happening). Given your push to try it again, I transformed the variable and then merge in the old version (with the missing values) to compare and it looks good!

data act.stage27;
update act.stage26(obs=0) act.stage26;
by cusip;
if missing(jfreducedlast) then jfreducedlast=0;
output;
run;

proc sql;
create table act.stage28 as select a.*, b.jfreduced
from act.stage27 as a left join act.stage25 as b
on a.year_cusip = b.year_cusip;
quit;

Thanks for your help and patience!