topic As long as a column contains a value then the other column changes accordingly in SAS Programming

As long as a column contains a value then the other column changes accordingly

Jarvin99 — Tue, 05 Apr 2022 07:38:16 GMT

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.

Re: As long as a column contains a value then the other column changes accordingly

Kurt_Bremser — Tue, 05 Apr 2022 07:56:01 GMT

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.

Re: As long as a column contains a value then the other column changes accordingly

Ksharp — Tue, 05 Apr 2022 09:57:42 GMT

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;

Re: As long as a column contains a value then the other column changes accordingly

andreas_lds — Tue, 05 Apr 2022 10:47:16 GMT

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;