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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.