Determine result across multiple records

Solved
Occasional Contributor
Posts: 5

Determine result across multiple records

[ Edited ]

I am attempting to analyze patient data.
There are multiple visits for each patient. I want to know whether a patient had a certain result during any of their visits. For example, you go to the doctor multiple times and want to know if you were marked as overweight at any point.

Accepted Solutions
Solution
‎04-08-2016 10:02 AM
Super User
Posts: 6,754

Re: Determine result across multiple records

Here's an example of the type of coding you are likely to use:

proc sort data=mydata;

by patient;

run;

data want;

set mydata;

by patient;

if first.patient then weight_condition='N';

if weight > 300 then weight_condition='Y';

if last.patient;

retain weight_condition;

run;

You will end up with just one observation per patient, with the new variable(s) being part of that observation.  You can code any IF/THEN conditions you would like to assign values to new variables, but remember to add any new variables to the RETAIN statement.

All Replies
Super User
Posts: 23,683

Re: Determine result across multiple records

Please post sample input and output data. Especially if you need help with coding.

Occasional Contributor
Posts: 5

Re: Determine result across multiple records

[ Edited ]

Here is an example of the data I am considering.

id  cat  test  result  date

1  chd  wgt   y      01/01/2016

1  chd  hgt   n      01/01/2016

1  chd  wgt   n      02/02/2016

2  chd  wgt   y      01/01/2016

2  chd  wgt   y      01/02/2016

2  chd  hgt   n      01/02/2016

.

.

.

I need to know if at any point a patient had a "y" result, regardless of the test.

Does that make sense?

Thanks again!

Super User
Posts: 13,508

Re: Determine result across multiple records

Slightly more specific code, uses the format YN in my other post.

data have;
informat date mmddyy10.;
format date mmddyy10.;
input id \$  cat \$  test \$ result \$  date;
datalines;
1  chd  wgt   y      01/01/2016
1  chd  hgt   n      01/01/2016
1  chd  wgt   n      02/02/2016
2  chd  wgt   y      01/01/2016
2  chd  wgt   y      01/02/2016
2  chd  hgt   n      01/02/2016
;
run;

data tab; /* to build a report table from*/
set have;
AnyYes = (result='y');
If test = 'wgt' then WgtYes = (result='y');
If test = 'hgt' then HgtYes = (result='y');
label
AnyYes = 'Any y test result'
WgtYes = 'Wgt test y result'
HgtYes = 'Hgt test y result'
;
run;

/* and a quicky report*/
proc tabulate data=tab;
class id;
class test;
var AnyYes WgtYes  HgtYes;
table id ,
(AnyYes WgtYes  HgtYes)* max=''*f=YN.
;
run;

Solution
‎04-08-2016 10:02 AM
Super User
Posts: 6,754

Re: Determine result across multiple records

Here's an example of the type of coding you are likely to use:

proc sort data=mydata;

by patient;

run;

data want;

set mydata;

by patient;

if first.patient then weight_condition='N';

if weight > 300 then weight_condition='Y';

if last.patient;

retain weight_condition;

run;

You will end up with just one observation per patient, with the new variable(s) being part of that observation.  You can code any IF/THEN conditions you would like to assign values to new variables, but remember to add any new variables to the RETAIN statement.

Super User
Posts: 13,508

Re: Determine result across multiple records

As an alternative to @Astounding's pseudo code build dichotomous variables valued 1/0 for yes/no for each condition of interest.

A Maximum value of 1 in a summary means "yes ever told/diagnosed/whater", sum would be number of times told, mean would be percent of times told.

data temp;
set have;
WeightWarn = (ToldOverWeight = 'YES');
run;
Where "ToldOverWeight" is the variable in your data that has that information and ='YES' is whatever code might be there.

Proc format library=work;
value YN;
1 = 'Yes'
0 = 'No';
run;

/*And a report :*/
proc tabulate data=temp;
class patientid;
var WeightWarn;
table patientid, Weightwarn='Weight Warning Status'*(max=''*f=YN. sum="Times Told Overweight"*f=best4. mean="% times told"*f=percent8.1);
run;
Posts: 3,167

Re: Determine result across multiple records

Not knowing your data and other restrains, it is hard to script any code, As a possible quick & dirty way(suppose 300 is overweight);

proc sql;
select * from have
group by patient_id
having sum(weight>=300)>0
;
quit;
Super User
Posts: 10,766

Re: Determine result across multiple records

data have;
informat date mmddyy10.;
format date mmddyy10.;
input id \$  cat \$  test \$ result \$  date;
datalines;
1  chd  wgt   y      01/01/2016
1  chd  hgt   n      01/01/2016
1  chd  wgt   n      02/02/2016
2  chd  wgt   y      01/01/2016
2  chd  wgt   y      01/02/2016
2  chd  hgt   n      01/02/2016
;
run;

proc sort data=have;
by id date result;
run;
data temp;
set have;
by id date;
if last.date;
run;
proc sql;
create table want as
select *
from temp
group by id
having sum(result='y')=count(*);
quit;

Occasional Contributor
Posts: 5