topic Re: How to get maximum and minimum reading for a person with respective dates in SAS Programming

How to get maximum and minimum reading for a person with respective dates

DeepakSwain — Thu, 03 Nov 2016 15:29:20 GMT

Hi there,

I am trying to generate record of each person having maximum and minimum bp with respective dates. Sample of dataset is as follows:

data test;
input id bp date $ ;
cards;
1 100 20160110
2 110 20160111
3 150 20160112
1 110 20160112
2 130 20160113
3 130 20160114
1 140 20160113
2 150 20160114
3 120 20160115
1 120 20160114
2 120 20160115
3 160 20160116
1 150 20160115
2 140 20160116
3 130 20160117
;
run;

Wanted:

id max_bp date_having_max_bp min_bp date_having_min_bp

1 150 20160115 100 20160110

Can somebody help me to do that. Thank you in advance for your kind reply.

Re: How to get maximum and minimum reading for a person with respective dates

RW9 — Thu, 03 Nov 2016 15:36:18 GMT

Hi:

proc sort data=test;
  by id bp;
run;

data want (drop=bp date);
  set test;
  by id;
  retain min_bp min_date_bp max_bp max_date_bp;
  if first.id then do;
    min_bp=bp;
    min_date_bp=date;
  end;
  if last.id then do;
    max_bp=bp;
    max_date_bp=date;
    output;
  end;
run;

Re: How to get maximum and minimum reading for a person with respective dates

Kurt_Bremser — Thu, 03 Nov 2016 15:45:25 GMT

Data step solution:

proc sort data=test;
by id;
run;

data want;
set test;
by id;
retain max_bp date_having_max_bp min_bp date_having_min_bp;
format date_having_max_bp date_having_min_bp yymmddn8.;
if first.id
then do;
  max_bp = 0;
  min_bp = 1000;
end;
if bp > max_bp
then do;
  max_bp = bp;
  date_having_max_bp = date;
end;
if bp < min_bp
then do;
  min_bp = bp;
  date_having_min_bp = date;
end;
if last.id then output;
keep id max_bp date_having_max_bp min_bp date_having_min_bp;
run;

PS: @RW9's solution beats mine. Much neater.

Re: How to get maximum and minimum reading for a person with respective dates

ballardw — Thu, 03 Nov 2016 15:46:08 GMT

One way:

proc means data=test noprint nway;
   class id;
   var bp;
   output out=want (drop= _:)
     max= idgroup(max(bp) out[1] (date)=DateBpMax)
     min= idgroup(Min(bp) out[1] (date)=DateBPMin)
     /autolabel autoname;
run;
proc print noobs label;run;

The Nway suppresses an over max min across the ID values.

I didn't force to your variable names. There some pretty good reasons to use the statistic as a suffix depending on later processing needs.

Re: How to get maximum and minimum reading for a person with respective dates

DeepakSwain — Thu, 03 Nov 2016 15:53:00 GMT

Thank you for your quick reply as well as for providing solution in very simple language.

Re: How to get maximum and minimum reading for a person with respective dates

DeepakSwain — Thu, 03 Nov 2016 15:58:29 GMT

Thank you for providing me an alternative way to address my reporting process.