topic Re: By Group how to Identify if one of the Visit is Present however another Visit is missing. in SAS Programming

By Group how to Identify if one of the Visit is Present however another Visit is missing.

PC7 — Wed, 29 Jan 2025 04:07:20 GMT

data have;
input ID $1-3 Visit $5-7;
cards; 
101 v1
101 v2
101 v3
101 v4
101 v5
102 v1
102 v2
102 v3
102 v5
103 v1
103 v2
103 v3
103 v4
;
RUN;

Looking for participant ID if v5 is present, however, v4 is missing then we need to flag.

Expected Output should be

id	visit	flag
102	v1	Missing visit 4
102	v2	Missing visit 4
102	v3	Missing visit 4
102	v5	Missing visit 4

Re: By Group how to Identify if one of the Visit is Present however another Visit is missing.

Ksharp — Wed, 29 Jan 2025 05:59:40 GMT

data have;
input ID $1-3 Visit $5-7;
cards; 
101 v1
101 v2
101 v3
101 v4
101 v5
102 v1
102 v2
102 v3
102 v5
103 v1
103 v2
103 v3
103 v4
;
RUN;
proc sql;
create table want as
select *,ifc(sum(visit='v5') ne 0 and sum(visit='v4') eq 0,'Missing visit 4',' ') as flag
 from have
  group by id;
quit;

Re: By Group how to Identify if one of the Visit is Present however another Visit is missing.

Patrick — Wed, 29 Jan 2025 06:35:51 GMT

Below selects all IDs with a visit=5 and populates the "flag" if there is no visit=v4

data want;
	if _n_=1 then
		do;
/* 			dcl hash h1(dataset:'have'); */
			dcl hash h1(dataset:'have(where=(visit in ("v4","v5")))');
			h1.defineKey('id','visit');
			h1.defineDone();
		end;
	set have;
	if h1.check(key:id, key:'v5') = 0 then 
		do;
			if h1.check(key:id, key:'v4') ne 0 then flag='Missing visit 4';
			output;
		end;
run;

proc print data=want;
run;

Re: By Group how to Identify if one of the Visit is Present however another Visit is missing.

PC7 — Wed, 29 Jan 2025 06:59:20 GMT

Thank you so much for the quick solution. Both the codes am getting as expected result.

Re: By Group how to Identify if one of the Visit is Present however another Visit is missing.

PC7 — Wed, 29 Jan 2025 07:00:07 GMT

thank you Ksharp 🙂