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;
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;
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.
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;
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 ;
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 |
@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.
Agree. I wanted some fun with IFC 🙂 after all.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.