Create dataset that tells what visits are excluded for unique ID

[Born_Aioli]

I need some help regarding how to check a certain condition for every unique PatientId in my data. In my mock data here we have 4 unique PatientId's. There are 8 possible doctor visits that every patient should do. I want to check, for every unque patient, what visits that are not done.

PatientId VisitNumber
1	  2
1         3	
2	  1
2	  2
2         4
2         5
2         6
2         8
3	  1
3	  2
3	  3
3	  4
3         5
3         6
3         7
3         8
4	  1
4         6
4         8

Optimal output would be:

PatientId     VisitsExcluded
1             1,4,5,6,7,8
2             3,7
3             .            
4             2,3,4,5,7   

How would you do it? What to write instead of [missing code], or do you have any other go?

* VisitsExcluded (of VisitNumber 1-8);
proc sql;
	create table want as
	select PatientId, case 
		when VisitNumber [missing code]
		else "" end as EventSeq_missing
	from have
	group by PatientId;
quit;

[Kurt_Bremser · Accepted solution]

See this:

data have;
input PatientId VisitNumber;
datalines;
1	  2
1         3	
2	  1
2	  2
2         4
2         5
2         6
2         8
3	  1
3	  2
3	  3
3	  4
3         5
3         6
3         7
3         8
4	  1
4         6
4         8
;

data want;
set have;
by patientid;
length visits $15;
retain req_visits;
if first.patientid then req_visits = '11111111';
substr(req_visits,visitnumber,1) = ' ';
if last.patientid
then do;
  do visitnumber = 1 to 8;
    if substr(req_visits,visitnumber,1) = '1' then visits=catx(',',visits,visitnumber);
  end;
  output;
end;
keep patientid visits;
run;

[novinosrin]

data have;
input PatientId VisitNumber;
datalines;
1	  2
1         3	
2	  1
2	  2
2         4
2         5
2         6
2         8
3	  1
3	  2
3	  3
3	  4
3         5
3         6
3         7
3         8
4	  1
4         6
4         8
;


data want;
 array v(8) _temporary_ (1:8) ;
 retain k;
 if _n_=1 then  k=peekclong(addrlong(v(1)),64);
 do until(last.PatientId);
  set have;
  by PatientId;
  _n_=whichn(visitnumber,of v(*));
  if _n_ then call missing(v(_n_));
 end;
 VisitsExcluded=compbl(compress(strip(catx(' ',of v(*))),'.'));
 call pokelong(k,addrlong(v(1)),64);
 drop k visitnumber;
run;

[Ksharp]

data have;
input PatientId VisitNumber;
datalines;
1 2
1 3
2 1
2 2
2 4
2 5
2 6
2 8
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
4 1
4 6
4 8
;
proc sql;
create table id as
select distinct PatientID from have;

create table temp as
select *
from (select * from id,(select distinct VisitNumber from have))
except
select * from have;

create table want as
select a.*,b.VisitNumber
from id as a left join temp as b on a.PatientID=b.PatientID;
quit;
data final_want;
do until(last.PatientID);
set want;
by PatientID;
length VisitsExcluded $ 80;
VisitsExcluded=catx(',',VisitsExcluded,VisitNumber);
end;
drop VisitNumber;
run;