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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.