change the value of a flag within a group

[SAShole]

Hi SAS friends,

I have a dataset that is structured by site, subject, and visits. I have a flag eval to indicate whether an evaluation was performed. If eval='N' for any of visits I would like all visits to display 'N'. How would you approach this? 

Thank you in advance, 

data have;
input site $ subject visit & $ eval $;
datalines;
01  111  Screen   Y
01  111  Visit 1  N
01  111  Visit 2  Y
01  222  Screen   Y
01  222  Visit 1  Y
01  222  Visit 2  Y
02  333  Screen   Y
02  333  Visit 1  Y
02  333  Visit 2  N
02  444  Screen   Y
02  444  Visit 1  Y
02  444  Visit 2  Y
;

data want;
input site $ subject visit & $ eval $;
datalines;
01  111  Screen   N
01  111  Visit 1  N
01  111  Visit 2  N
01  222  Screen   Y
01  222  Visit 1  Y
01  222  Visit 2  Y
02  333  Screen   N
02  333  Visit 1  N
02  333  Visit 2  N
02  444  Screen   Y
02  444  Visit 1  Y
02  444  Visit 2  Y
;
proc print data = want;

[mklangley]

Here's one way:

data have;
input site $ subject visit & $ eval $;
datalines;
01  111  Screen   Y
01  111  Visit 1  N
01  111  Visit 2  Y
01  222  Screen   Y
01  222  Visit 1  Y
01  222  Visit 2  Y
02  333  Screen   Y
02  333  Visit 1  Y
02  333  Visit 2  N
02  444  Screen   Y
02  444  Visit 1  Y
02  444  Visit 2  Y
;
run;

/* Find the site/subject combinations for which EVAL should be N */
proc sql;
    create table visits_with_N as
    select distinct site
          ,subject
    from have
    where upcase(visit) like "VISIT%"
        and upcase(eval) = 'N';
quit;
    
/* Join those combinations to HAVE, and set EVAL to N where there is a match */
proc sql;
    create table want as
    select h.site
          ,h.subject
          ,h.visit
          ,case
               when v.subject is null
                   then h.eval
               else 'N'
           end as eval
    from have h
    left join visits_with_n v
        on h.site = v.site
        and h.subject = v.subject
    order by site, subject, visit, eval;
quit;

proc print data = want;

By the way, thank you for the clear question and run-able sample data.

[r_behata · Accepted solution]

data have;
input site $ subject visit & $ eval $;
datalines;
01  111  Screen   Y
01  111  Visit 1  N
01  111  Visit 2  Y
01  222  Screen   Y
01  222  Visit 1  Y
01  222  Visit 2  Y
02  333  Screen   Y
02  333  Visit 1  Y
02  333  Visit 2  N
02  444  Screen   Y
02  444  Visit 1  Y
02  444  Visit 2  Y
;
run;



data want;
	merge have(in=a) have(where=(eval_='N') in=b rename=(eval=eval_));
	if a;
	by site subject;
	
	eval=coalescec(eval_,eval);
	
	drop eval_;
run;

[novinosrin]

A nice question to get some hands on practice for any HASH beginner--

data have;
input site $ subject visit & $ eval $;
datalines;
01  111  Screen   Y
01  111  Visit 1  N
01  111  Visit 2  Y
01  222  Screen   Y
01  222  Visit 1  Y
01  222  Visit 2  Y
02  333  Screen   Y
02  333  Visit 1  Y
02  333  Visit 2  N
02  444  Screen   Y
02  444  Visit 1  Y
02  444  Visit 2  Y
;

data want ;
  if _n_=1 then do ;
   dcl hash h(dataset:"have(where=(eval='N')") ;
   h.definekey('site','subject') ;
   h.definedone() ;
  end ;
  set have ;
  eval=ifc(h.check()=0,'N',eval) ;
 run ;
 
 proc print noobs ; run ;

RESULT

site	subject	visit	eval
01	111	Screen	N
01	111	Visit 1	N
01	111	Visit 2	N
01	222	Screen	Y
01	222	Visit 1	Y
01	222	Visit 2	Y
02	333	Screen	N
02	333	Visit 1	N
02	333	Visit 2	N
02	444	Screen	Y
02	444	Visit 1	Y
02	444	Visit 2	Y

[Patrick]

@novinosrin Why not just use the find() method?

data want;
  if _n_=1 then
    do;
      dcl hash h(dataset:"have(where=(eval='N')");
      h.definekey('site','subject');
      h.defineData('eval');
      h.definedone();
    end;

  set have;
  _rc=h.find();
/*  if h.find() then;*/
run;

The syntax with the if condition is just a trick for calling the find method without the need to capture the return value in an additional variable. 

You could also just code: h.find(); but this then clutters the SAS log with return codes from the method call.

[novinosrin]

Agree. I wanted some fun with IFC 🙂 after all.

[SAShole]

Well done -- I love a good hash solution!

[tarheel13]

proc sql;
	create table n_ever as select subject, max(eval='N') as N_ever
		from have
		group by subject;
quit;

proc sql;
	create table want
	as select h.site,h.subject,h.visit,
	case when N_ever=1 then 'N'
	else 'Y' end as eval
		from have as h
		left join
		n_ever as n
		on h.subject=n.subject;
quit;

[Ksharp]

data have;
input site $ subject visit & $ eval $;
datalines;
01  111  Screen   Y
01  111  Visit 1  N
01  111  Visit 2  Y
01  222  Screen   Y
01  222  Visit 1  Y
01  222  Visit 2  Y
02  333  Screen   Y
02  333  Visit 1  Y
02  333  Visit 2  N
02  444  Screen   Y
02  444  Visit 1  Y
02  444  Visit 2  Y
;

proc sql;
create table want as
select site,subject,visit,min(eval) as eval
 from have
  group by site,subject
   order by 1,2,3;
quit;