How to get minimum value for the below data?

[rajeshalwayswel]

data have;
input subject visitnum results;
cards;
101 1 1.1
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
run;

Required Output:
subject visitnum results required
101       1        3.9    .
101       2        2.2    3.9
101       3        2.2    2.2
101       4        2.0    2.2
101       5        0.9    2.0
101       6        2.0    0.9

The logicis for subject we need to get minimum value before to the visitnum

[sivastat08]

Hi,

data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
run;

Data want;
set have;
required=lag(results);
run;

[PeterClemmensen]

Where does 3.9 come from?

[sivastat08]

Hello sir,

user might have

miss spelled instead of (101 1 1.1)=(101 1 3.9)

data have;
input subject visitnum results;
cards;
101 1 1.1
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
run;

[rajeshalwayswel]

oh, it's 1.1

[Kurt_Bremser]

Use a retained variable, by-group processing, and the min() function:

(based on your example data)

data have;
input subject visitnum results;
cards;
101 1 1.1
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
;

data want;
set have;
by subject;
retain required;
if first.subject
then required = min(results,9999); * use sufficiently large value here;
else required = min(results,required);
run;

---

@rajeshalwayswel wrote:

data have;
input subject visitnum results;
cards;
101 1 1.1
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
run;

Required Output:
subject visitnum results required
101       1        3.9    .
101       2        2.2    3.9
101       3        2.2    2.2
101       4        2.0    2.2
101       5        0.9    2.0
101       6        2.0    0.9

The logicis for subject we need to get minimum value before to the visitnum

---

[Ksharp]

data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
;
run;
proc sql;
create table want as
select *,(select min(results) from have 
where subject=a.subject and visitnum < a.visitnum) as min
from have as a;
quit;

[rajeshalwayswel]

Hi,

It's work for the some the records but when I haves same visitnum it not working, Could you please have a look on the below data.


data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 1.1
101 3 2.2
101 4 2.0
101 5 0.9
101 5 0.9
;
run;

Required:

101 1 3.9  .
101 2 1.1  3.9
101 3 2.2  1.1
101 4 2.0  1.1
101 5 0.9  1.1
101 5 0.9   0.9

[Kurt_Bremser · Accepted solution]

Using my previous code, with a little twist:

data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 1.1
101 3 2.2
101 4 2.0
101 5 0.9
101 5 0.9
;

data want;
set have;
by subject;
retain required;
if first.subject
then required = .;
output;
if first.subject
then required = min(results,9999); * use sufficiently large value here;
else required = min(results,required);
run;

proc print data=want noobs;
run;

Result:

subject    visitnum    results    required

  101          1         3.9          .   
  101          2         1.1         3.9  
  101          3         2.2         1.1  
  101          4         2.0         1.1  
  101          5         0.9         1.1  
  101          5         0.9         0.9  

[rajeshalwayswel]

Thanks so much Kurtbremser. It's worked....

[Ksharp]

Yes. Try @Tom  or @Kurt_Bremser  's code .

[FreelanceReinh]

Hi @rajeshalwayswel,

I would use a slightly modified DATA step approach, for example one combining the suggestions from @Kurt_Bremser and @sivastat08:

data want;
set have;
by subject;
retain required 0;
required=min(required,lag(results));
if first.subject then required=.;
run;

Or, similarly, without the LAG function:

data want;
set have;
by subject;
retain required;
if first.subject then required=.;
output;
required=min(required,results);
run;

(Note: The purpose of the arbitrary initial value 0 for required in the first solution is just to avoid the note "Missing values were generated ..." for the very first observation.)

Both variants assume that dataset HAVE is sorted by subject and chronologically within each subject. The advantage of @Ksharp's solution is that it does not require HAVE to be sorted. It assumes that visits of a subject are numbered chronologically, though.

[Tom]

Your data example works with a simple LAG() function call.

Perhaps you need to update your example where the min is not just the value of the previous observation?

data want;
  set have ;
  by subject ;
  want = min(want,lag(results));
  if first.subject then want=.;
  retain want;
run;

Here is slighly modified data demonstrates the difference.

data have;
input subject visitnum results required;
cards;
101       1        3.9    .
101       2        2.2    3.9
101       3        2.0    2.2
101       4        2.2    2.0
101       5        0.9    2.0
101       6        2.0    0.9
;
data want;
  set have ;
  by subject ;
  lag_results=lag(results);
  want = min(want,lag(results));
  if first.subject then call missing(want,lag_results);
  retain want;
run;

Notice the different result for VISTNUM=5?

                                                       lag_
Obs    subject    visitnum    results    required    results    want

 1       101          1         3.9          .          .         .
 2       101          2         2.2         3.9        3.9       3.9
 3       101          3         2.0         2.2        2.2       2.2
 4       101          4         2.2         2.0        2.0       2.0
 5       101          5         0.9         2.0        2.2       2.0
 6       101          6         2.0         0.9        0.9       0.9