How to find the minimum value compared to previous observation for same subject

[jksthomas]

Below is my data in column SUBJ, B and VALUE. I need MINIM column with the min(VALUE) compared to previous observations for same subject. Can Any one help?

SUBJ	SEQ	VALUE	MINIM	  This is how MIN value is calculated
1004	1	35.58	-	 - There is no obs previous and should appear blank
1004	2	32.69	35.58	 - Only one obs available prior so 35.58 is min
1004	3	26.15	32.69	 - Compared to obs 1, 2 value of 32.69 is min
1004	4	21.77	26.15	 - Compared to obs 1, 2 and 3 value of 26.15 is min
1004	5	19.47	21.77	 - Compared to 1, 2,3 and 4 value of 21.77 is min
1004	6	19.52	19.47	 - Compared to 1, 2,3,4 and 5 value of 19.47 is min
1004	7	13.71	19.47	 - Compared to 1, 2,3,4,5 and 6 for this subject value of 19.47 is min
1004	8	13.7	13.71	
1004	9	13.97	13.7	
1004	10	24.28   13.7
1005	1	22.57	-	 - There is no previous obs for this subject and should appear blank
1005	2	10.21	22.57	 - Only one row available previous so 22.57 is min
1005	3	10.35	10.21	 - Compared to 1, 2 value of 10.21 is min
1005	4	10.35	10.21	 - Compared to 1, 2 and 3 value of 10.21 is min
1005	5	10.35	10.21	 - Compared to 1, 2,3 and 4 for this subject value of 21.77 is minimum
1005	6	9.76	10.21

[novinosrin]

data have;
input SUBJ	SEQ	VALUE; *	MINIM	  This is how MIN value is calculated;
cards;
1004	1	35.58	-	 - There is no obs previous and should appear blank
1004	2	32.69	35.58	 - Only one obs available prior so 35.58 is min
1004	3	26.15	32.69	 - Compared to obs 1, 2 value of 32.69 is min
1004	4	21.77	26.15	 - Compared to obs 1, 2 and 3 value of 26.15 is min
1004	5	19.47	21.77	 - Compared to 1, 2,3 and 4 value of 21.77 is min
1004	6	19.52	19.47	 - Compared to 1, 2,3,4 and 5 value of 19.47 is min
1004	7	13.71	19.47	 - Compared to 1, 2,3,4,5 and 6 for this subject value of 19.47 is min
1004	8	13.7	13.71	
1004	9	13.97	13.7	
1004	10	24.28   13.7
1005	1	22.57	-	 - There is no previous obs for this subject and should appear blank
1005	2	10.21	22.57	 - Only one row available previous so 22.57 is min
1005	3	10.35	10.21	 - Compared to 1, 2 value of 10.21 is min
1005	4	10.35	10.21	 - Compared to 1, 2 and 3 value of 10.21 is min
1005	5	10.35	10.21	 - Compared to 1, 2,3 and 4 for this subject value of 21.77 is minimum
1005	6	9.76	10.21
;

data want;
 do _n_=1 by 1 until(last.subj);
  set have;
  by subj;
  array t(99999) _temporary_;
  if _n_>1 then min=min(of t(*));
  t(_n_)=value;
  output;
 end;
 call missing(of t(*));
run;

[novinosrin]

And if you want a SQL solution

data have;
input SUBJ	SEQ	VALUE; *	MINIM	  This is how MIN value is calculated;
cards;
1004	1	35.58	-	 - There is no obs previous and should appear blank
1004	2	32.69	35.58	 - Only one obs available prior so 35.58 is min
1004	3	26.15	32.69	 - Compared to obs 1, 2 value of 32.69 is min
1004	4	21.77	26.15	 - Compared to obs 1, 2 and 3 value of 26.15 is min
1004	5	19.47	21.77	 - Compared to 1, 2,3 and 4 value of 21.77 is min
1004	6	19.52	19.47	 - Compared to 1, 2,3,4 and 5 value of 19.47 is min
1004	7	13.71	19.47	 - Compared to 1, 2,3,4,5 and 6 for this subject value of 19.47 is min
1004	8	13.7	13.71	
1004	9	13.97	13.7	
1004	10	24.28   13.7
1005	1	22.57	-	 - There is no previous obs for this subject and should appear blank
1005	2	10.21	22.57	 - Only one row available previous so 22.57 is min
1005	3	10.35	10.21	 - Compared to 1, 2 value of 10.21 is min
1005	4	10.35	10.21	 - Compared to 1, 2 and 3 value of 10.21 is min
1005	5	10.35	10.21	 - Compared to 1, 2,3 and 4 for this subject value of 21.77 is minimum
1005	6	9.76	10.21
;

proc sql;
create table want as
select a.subj,a.seq,a.value,min(b.value) as min
from have a left join have b
on a.subj=b.subj
and b.seq<a.seq
group by a.subj,a.seq,a.value
order by a.subj,a.seq;
quit;

[FreelanceReinh · Accepted solution]

Hi @jksthomas and welcome to the SAS Support Communities!

Here's another DATA step solution:

data want;
do until(last.subj);
  set have;
  by subj seq;
  output;
  minim=min(minim,value);
end;
run;

[novinosrin]

Indeed more efficient and tactfully observed(the values), makes me realise my lethargy. Need to change my coffee brand big time. Kudos!

[jksthomas]

---

@novinosrin wrote:

Indeed more efficient and tactfully observed(the values), makes me realise my lethargy. Need to change my coffee brand big time. Kudos!

---

@novinosrin @FreelanceReinh Both of your prog worked well. Thanks a lot.

[jksthomas]

---

@jksthomas wrote:

---

@novinosrin wrote:

Indeed more efficient and tactfully observed(the values), makes me realise my lethargy. Need to change my coffee brand big time. Kudos!

---

@novinosrin @FreelanceReinh Both of your prog worked well. Thanks a lot.

---

One more question: Can I get one more additonal row to display which seq from the subj is taken as minim?

[FreelanceReinh]

---

@jksthomas wrote:

---

---

One more question: Can I get one more additonal row to display which seq from the subj is taken as minim?

---

Sure (assuming you mean: additional column, i.e. variable😞

data want;
do until(last.subj);
  set have;
  by subj seq;
  output;
  if value<minim | first.subj then do;
    minim=value;
    seq_minim=seq;
  end;
end;
run;

In the case of tied minimum values, e.g., if in your sample data the observation with SUBJ=1005, SEQ=4 had VALUE=10.21 (instead of 10.35), the above code would store the first SEQ with a VALUE of 10.21 in SEQ_MINIM, i.e., SEQ_MINIM=2 for the last four observations. By replacing the "<" sign in the IF condition with "<=" you could obtain the last SEQ instead, i.e., SEQ_MINIM=4 in the last two observations (and, of course, still SEQ_MINIM=2 in the two preceding observations).

[jksthomas]

hi,

Thanks again for you help

Sorry i mean to say new variable.

Prog works fine from 3rd row, however, for the second row SEQ_MINIM is blank. Is there a way to display seq = 1 .

SUBJ	VALUE	SEQ	MINIM	SEQ_MINIM
1001003	22.57	1		
1001003	10.21	2	22.57	
1001003	10.35	3	10.21	2
1001003	10.35	4	10.21	2
1001003	10.35	5	10.21	2
1001003	9.76	6	10.21	2

---

[FreelanceReinh]

---

@jksthomas wrote:

Prog works fine from 3rd row, however, for the second row SEQ_MINIM is blank. Is there a way to display seq = 1 .

---

---

This does not happen if the input dataset (HAVE) does not already contain the derived variables (MINIM, SEQ_MINIM). So, make sure that you don't run the step, e.g., on the result of a previous run (like data have; set have; ...). If dataset HAVE for some reason contains variables MINIM or SEQ_MINIM, just drop them via a dataset option:

set have(drop=minim seq_minim);