My data is like:
id | year | gvkey | indicator |
1 | 2011 | 1000 | 0 |
1 | 2012 | 1000 | 1 |
1 | 2015 | 2000 | 0 |
2 | 2010 | 1000 | 0 |
2 | 2010 | 3000 | 0 |
2 | 2013 | 3000 | 1 |
2 | 2013 | 2000 | 0 |
3 | 2017 | 2000 | 0 |
3 | 2018 | 2000 | 1 |
2 | 2018 | 3000 | 0 |
Group by id and year, I want the new_indicator to depend on the indicator's value. As long as the indicator has a 0 in an id's particular year, the new_indicator of this id becomes 1 in that year, regardless of how many 0 or 1 that id has for the indicator in the year.
So, the intended outcome should be:
id | year | gvkey | indicator | new_indicator |
1 | 2011 | 1000 | 0 | 1 |
1 | 2012 | 1000 | 1 | 0 |
1 | 2015 | 2000 | 0 | 1 |
2 | 2010 | 1000 | 0 | 1 |
2 | 2010 | 3000 | 0 | 1 |
2 | 2013 | 3000 | 1 | 1 |
2 | 2013 | 2000 | 0 | 1 |
3 | 2017 | 2000 | 0 | 1 |
3 | 2018 | 2000 | 1 | 1 |
3 | 2018 | 3000 | 0 | 1 |
Thank you.
data have;
infile datalines expandtabs;
input id year gvkey indicator;
datalines;
1 2011 1000 0
1 2012 1000 1
1 2015 2000 0
2 2010 1000 0
2 2010 3000 0
2 2013 3000 1
2 2013 2000 0
3 2017 2000 0
3 2018 2000 1
3 2018 3000 0
;
proc sql;
create table want as
select *,min(indicator)=0 as new_indicator
from have
group by id,year;
quit;
See this:
data have;
infile datalines dlm="09"x;
input id $ year gvkey $ indicator;
datalines;
1 2011 1000 0
1 2012 1000 1
1 2015 2000 0
2 2010 1000 0
2 2010 3000 0
2 2013 3000 1
2 2013 2000 0
3 2017 2000 0
3 2018 2000 1
3 2018 3000 0
;
data want;
merge
have
have (
keep=id year indicator
rename=(indicator=_ind)
where=(_ind = 0)
in=zero
)
;
by id year;
new_indicator = zero;
drop _ind;
run;
Please post your data in the future in a data step with datalines like I did, so we don't have to do that for you. It also prevents any misunderstandings about raw content, variable types and attributes.
data have;
infile datalines expandtabs;
input id year gvkey indicator;
datalines;
1 2011 1000 0
1 2012 1000 1
1 2015 2000 0
2 2010 1000 0
2 2010 3000 0
2 2013 3000 1
2 2013 2000 0
3 2017 2000 0
3 2018 2000 1
3 2018 3000 0
;
proc sql;
create table want as
select *,min(indicator)=0 as new_indicator
from have
group by id,year;
quit;
And another solution:
data work.want;
set work.have;
if _n_ = 1 then do;
declare hash h(dataset: 'work.have(keep= id year indicator where= (indicator=0))');
h.defineKey('id', 'year');
h.defineDone();
end;
new_indicator = h.check() = 0;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.