Update Multiple rows Based on one Row’s Value

[DavidPhillips2]

Is there a simple way to update something like this?

I have one row per semester.  I have three semesters in a year.  If any of the semesters has a value of X then set a variable to true for all three rows otherwise set it to false.

I could use a loop but I am thinking there is something simpler and efficient.

Update multiple rows based on a value.

[PeterClemmensen]

You can do something like this

data have;
input year semester $;
datalines;
2017 A
2017 B
2017 C
2018 A
2018 X
2018 C
;

proc sql;
   create table want as
   select *
   from have
   group by year
   having sum(semester='X') > 0;
quit;

[DavidPhillips2]

The idea is to keep the same number of rows.

case 1

One or more semesters have the flag so all values of column wanted flag is Yes.

Term     Original Flag   Wanted Flag

Spring                      1                 Yes

Fall                           1                 Yes

Summer                   0                  Yes

case2

No semester has the value so all of column wanted flag is no.

Term     Original Flag   Wanted Flag

Spring                      0                  No

Fall                           0                  No

Summer                   0                  No

[Reeza]

Sort your data by descending original flag. And then only check on the first row to assign the value.

proc sort data=have;
by ID descending originalFlag;
run;

data want;
set have;
by id descending originalflag;
if first.id and originalFlag=1 then wantedflag='Yes' else if first.ID then wantedFlag='No';

retain wantedFlag;
run;

[novinosrin]

@Reeza Mam, some humor- did you miss your coffee or didn't get enough sleep. I would have never ever thought you would miss out on your favorite max function that you answered a similar query just yesterday and many a times flawlessly. Hmm, even the smartest tend to miss out at times? or was there a purpose to sort? Starbucks is better than tim hortons imho. 🙂

So plagiarizing your favorite idea, I offer the following

using @PeterClemmensen sample

data have;
input Year Term $ Original_Flag;
datalines;
2017 Spring 0
2017 Fall 0
2017 Summer 0
2018 Spring 1
2018 Fall 1
2018 Summer 0
;
proc sql;
create table want as
select *,ifc(max(Original_Flag)>0,'TRUE','FALSE') as wanted_flag
from have
group by year;
quit;

[Reeza]

It depends on the case - in this case it's a remerge with the main data so that's still two passes of the data. If you have do multiple passes it doesn't matter much which approach you use. In other cases, it's not because it's a summarization of the output instead.

[novinosrin]

Fair enough 🙂 I was surprised though 

[novinosrin]

data have;
input Year Term $ Original_Flag;
datalines;
2017 Spring 0
2017 Fall 0
2017 Summer 0
2018 Spring 1
2018 Fall 1
2018 Summer 0
;
data want;
merge have have(in =b keep=Original_Flag year rename=(Original_Flag=_f) where=(_f=1) );
by year;
retain wanted_flag '     ';
if b then   wanted_flag='TRUE';else wanted_flag='FALSE';
drop _f;
run;

[DavidPhillips2]

I'm going to go with this method.

data have;
input Year Term $ Original_Flag;
datalines;
2017 Spring 0
2017 Fall 0
2017 Summer 0
2018 Spring 1
2018 Fall 1
2018 Summer 0
;
proc sql;
create table want as
select *,ifc(max(Original_Flag)>0,'TRUE','FALSE') as wanted_flag
from have
group by year;
quit;

[novinosrin]

Good morning :), That's the reason I love the banter with Reeza saying Starbucks coffee is better than Tim hortons.

[Reeza]

---

@novinosrin wrote:

Good morning :), That's the reason I love the banter with Reeza saying Starbucks coffee is better than Tim hortons.

---

lol. Reeza doesn't drink coffee. The one time she drank coffee was before her first SAS presentation and she was too nervous and hadn't slept. And then talked a mile a minute during the presentation. It was selected for coders corner in Global Forum though 🙂

[PeterClemmensen]

Ah, I see. This will do then.

data have;
input Year Term $ Original_Flag;
datalines;
2017 Spring 0
2017 Fall 0
2017 Summer 0
2018 Spring 1
2018 Fall 1
2018 Summer 0
;

data want;
 if _N_ = 1 then do;
 declare hash h(dataset:"have(where=(Original_Flag=1))");
 h.defineKey('year');
 h.defineDone();
 end;
 
 set have;

 New_Flag="Yes";
 if h.check() ne 0 then New_Flag="No";
run;

[PeterClemmensen]

Or if you want to do so in a data step, here is a hash solution, that I quite like

data want;
   if _N_ = 1 then do;
      declare hash h(dataset:"have(where=(semester='X'))");
      h.defineKey('year');
      h.defineDone();
   end;
   
   set have;

   if h.check()=0;
run;

[Astounding · Accepted solution]

The idea of this is relatively simple (although there are a few ways to solve it).  Here's a readily understandable way:

proc sort data=have;

by year;

run;

data want;

do until (last.year);

   set have;

   by year;

   if original_flag then wanted_flag='True';

end;

do until (last.year);

   set have; 

   by year;

   output;

end;

run;

There's a little bit of confusion here about what the variable names are and what values they take on ... but the program can easily be adjusted to accommodate.