topic Re: Help with creating a variable in SAS Programming

Help with creating a variable

shalmali — Sun, 02 Sep 2012 19:46:25 GMT

I have a data which has the following information:

Company year ADD OUT Change

A 2001 0 0 1

A 2002 0 0 1

A 2003 0 0 1

A 2004 1 0 1

A 2005 0 0 0

A 2006 0 1 0

A 2007 0 0 0

A 2008 0 0 1

B 2001 0 0 0

B 2001 0 0 1

B 2002 0 0 0

B 2003 1 0 1

B 2004 0 0 0

B 2005 0 0 0

B 2006 0 1 0

B 2007 0 0 0

B 2008 0 0 0

B 2009 0 0 1

I am trying to create two new variable NEW1 and NEW2. If ADD =1 and CHANGE =1 in the third year prior to ADD =1 then all the three years prior to ADD =1 will be 1 for NEW1. If CHANGE =1 in the second year prior year to ADD =1 then NEW1 = 1 for two years prior to ADD =1. If CHANGE =1 in the year prior to ADD = 1 then NEW1 = 1 for the year prior to ADD =1. Similarly, if OUT =1 and CHANGE =1 in the third year following OUT =1, then NEW2 =1 for all the three years following OUT=1. In other words, I am trying to create a dataset which would look like the following:

Company year ADD OUT Change NEW1 NEW2

A 2001 0 0 1 1 0

A 2002 0 0 1 1 0

A 2003 0 0 1 1 0

A 2004 1 0 1 0 0

A 2005 0 0 0 0 0

A 2006 0 1 0 0 0

A 2007 0 0 0 0 1

A 2008 0 0 1 0 1

A 2009 0 0 0 0 0

B 2000 0 0 0 0 0

B 2001 0 0 1 1 0

B 2002 0 0 0 1 0

B 2003 1 0 1 0 0

B 2004 0 0 0 0 0

B 2005 0 0 0 0 0

B 2006 0 1 0 0 0

B 2007 0 0 0 0 1

B 2008 0 0 0 0 1

B 2009 0 0 1 0 1

Any suggessions will be helpful.

Re: Help with creating a variable

PGStats — Sun, 02 Sep 2012 22:26:18 GMT

SQL solution :

data have;
input Company $ year ADD OUT Change;
datalines;
A               2001     0               0          1
A               2002     0               0          1
A               2003     0               0          1
A               2004     1               0          1
A               2005     0               0          0
A               2006     0               1          0
A               2007     0               0          0
A               2008     0               0          1
B               2000     0               0          0
B               2001     0               0          1
B               2002     0               0          0
B               2003     1               0          1
B               2004     0               0          0
B               2005     0               0          0
B               2006     0               1          0
B               2007     0               0          0
B               2008     0               0          0
B               2009     0               0          1
;

proc sql;

create table ADD as
select a.company, a.year-1 as ADDyear, b.year as changeYear
from have as a inner join have as b

on a.company=b.company and a.ADD and b.Change and
b.year between a.year-3 and a.year-1;

create table OUT as
select a.company, a.year+1 as OUTyear, b.year as changeYear
from have as a inner join have as b
on a.company=b.company and a.OUT and b.Change and
a.year between b.year-3 and b.year-1;

create table want as
select H.*,
     exists (select * from ADD where company=H.company and
          H.year between changeYear and ADDyear) as NEW1,
     exists (select * from OUT where company=H.company and
          H.year between OUTyear and changeYear) as NEW2
from have as H;

drop table ADD, OUT;
select * from want;
quit;

Re: Help with creating a variable

shalmali — Sun, 02 Sep 2012 23:10:14 GMT

Thank you PGstats for the solution.