topic Re: Determine result across multiple records in SAS Programming

Determine result across multiple records

Elise8305 — Thu, 07 Apr 2016 19:23:18 GMT

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.

Re: Determine result across multiple records

Reeza — Thu, 07 Apr 2016 19:23:43 GMT

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

Re: Determine result across multiple records

Astounding — Thu, 07 Apr 2016 19:30:48 GMT

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.

Re: Determine result across multiple records

ballardw — Thu, 07 Apr 2016 19:47:53 GMT

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;

Re: Determine result across multiple records

Haikuo — Thu, 07 Apr 2016 20:52:38 GMT

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;

Re: Determine result across multiple records

Elise8305 — Thu, 07 Apr 2016 21:47:10 GMT

Thank you all for your comments.

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!

Re: Determine result across multiple records

ballardw — Thu, 07 Apr 2016 22:06:15 GMT

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;

Re: Determine result across multiple records

Ksharp — Fri, 08 Apr 2016 01:20:42 GMT

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;

Re: Determine result across multiple records

Elise8305 — Fri, 08 Apr 2016 14:03:44 GMT

Thank you everyone. I used the code from Astounding and got the output I needed. Thanks again!