topic Re: Update Multiple rows Based on one Row’s Value in SAS Programming

Update Multiple rows Based on one Row’s Value

DavidPhillips2 — Thu, 10 Jan 2019 20:37:57 GMT

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.

Re: Update Multiple rows Based on one Row’s Value

PeterClemmensen — Thu, 10 Jan 2019 20:43:47 GMT

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;

Re: Update Multiple rows Based on one Row’s Value

PeterClemmensen — Thu, 10 Jan 2019 20:46:34 GMT

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;

Re: Update Multiple rows Based on one Row’s Value

DavidPhillips2 — Thu, 10 Jan 2019 20:54:08 GMT

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

Re: Update Multiple rows Based on one Row’s Value

Reeza — Thu, 10 Jan 2019 20:57:21 GMT

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;

Re: Update Multiple rows Based on one Row’s Value

Astounding — Thu, 10 Jan 2019 21:06:44 GMT

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.

Re: Update Multiple rows Based on one Row’s Value

PeterClemmensen — Thu, 10 Jan 2019 21:11:14 GMT

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;

Re: Update Multiple rows Based on one Row’s Value

novinosrin — Thu, 10 Jan 2019 21:48:18 GMT

@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;

Re: Update Multiple rows Based on one Row’s Value

Reeza — Thu, 10 Jan 2019 21:28:02 GMT

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.

Re: Update Multiple rows Based on one Row’s Value

novinosrin — Thu, 10 Jan 2019 21:29:06 GMT

Fair enough 🙂 I was surprised though

Re: Update Multiple rows Based on one Row’s Value

novinosrin — Thu, 10 Jan 2019 21:52:23 GMT

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;

Re: Update Multiple rows Based on one Row’s Value

DavidPhillips2 — Fri, 11 Jan 2019 14:10:50 GMT

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;

Re: Update Multiple rows Based on one Row’s Value

novinosrin — Fri, 11 Jan 2019 14:29:08 GMT

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

Re: Update Multiple rows Based on one Row’s Value

Reeza — Sat, 12 Jan 2019 00:51:47 GMT

@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 🙂