BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Born_Aioli
Calcite | Level 5

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Tourmaline | Level 20

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
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 341 views
  • 0 likes
  • 4 in conversation