topic Re: How to get minimum value for the below data? in New SAS User

How to get minimum value for the below data?

rajeshalwayswel — Tue, 26 Nov 2019 12:07:20 GMT

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

Re: How to get minimum value for the below data?

sivastat08 — Tue, 26 Nov 2019 12:19:46 GMT

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;

Re: How to get minimum value for the below data?

PeterClemmensen — Tue, 26 Nov 2019 12:20:03 GMT

Where does 3.9 come from?

Re: How to get minimum value for the below data?

sivastat08 — Tue, 26 Nov 2019 12:24:42 GMT

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;

Re: How to get minimum value for the below data?

rajeshalwayswel — Tue, 26 Nov 2019 12:25:06 GMT

oh, it's 1.1

Re: How to get minimum value for the below data?

Kurt_Bremser — Tue, 26 Nov 2019 12:26:33 GMT

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

Re: How to get minimum value for the below data?

Ksharp — Tue, 26 Nov 2019 12:47:51 GMT


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;

Re: How to get minimum value for the below data?

FreelanceReinh — Tue, 26 Nov 2019 17:01:57 GMT

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.

Re: How to get minimum value for the below data?

Tom — Tue, 26 Nov 2019 17:11:34 GMT

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

Re: How to get minimum value for the below data?

rajeshalwayswel — Thu, 28 Nov 2019 04:57:33 GMT

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

Re: How to get minimum value for the below data?

Kurt_Bremser — Thu, 28 Nov 2019 06:38:41 GMT

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

Re: How to get minimum value for the below data?

Ksharp — Thu, 28 Nov 2019 11:27:26 GMT

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

Re: How to get minimum value for the below data?

rajeshalwayswel — Fri, 29 Nov 2019 02:46:20 GMT

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