DATA Step, Macro, Functions and more

Determine result across multiple records

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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: 5,516

Re: Determine result across multiple records

Posted in reply to Elise8305

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.

View solution in original post


All Replies
Super User
Posts: 19,860

Re: Determine result across multiple records

Posted in reply to Elise8305

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 ]

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!

Super User
Posts: 11,343

Re: Determine result across multiple records

Posted in reply to Elise8305

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: 5,516

Re: Determine result across multiple records

Posted in reply to Elise8305

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: 11,343

Re: Determine result across multiple records

Posted in reply to Elise8305

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;
Respected Advisor
Posts: 3,156

Re: Determine result across multiple records

Posted in reply to Elise8305

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,044

Re: Determine result across multiple records

Posted in reply to Elise8305
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

Re: Determine result across multiple records

Posted in reply to Elise8305
Thank you everyone. I used the code from Astounding and got the output I needed. Thanks again!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 462 views
  • 1 like
  • 6 in conversation