topic Re: How to get smallest value in a variable based on their increasing order and dates in SAS Programming

How to get smallest value in a variable based on their increasing order and dates

Ravindra_ — Wed, 29 Jul 2020 12:41:31 GMT

I have a data as below and i need to derive the smallest value of the diameter

Example: In scr visit the diameter is 20 and the smallest value must be 20, like wise c2 diameter is 15 and smallest value must come as 15, c3 diameter is 10 and the smallest value must be 10, c4 diameter is 12 and smallest value must be 12, c5 diameter is 9 ans smallest value must be 9, c6 diameter is 10 and the smallest value must be 9, c7 diameter is 17 and smallest value must be 9, c8 diameter is 20 and smallest value must be 9, c9 diameter is 2 and smallest value must be 2.

1	subject	Asse visit	date	diameter
2	101	scr	1-Jan-20	20
3	101	c2	2-Jan-20	15
4	101	c3	3-Jan-20	10
5	101	c4	4-Jan-20	12
6	101	c5	5-Jan-20	9
7	101	c6	6-Jan-20	10
8	101	c7	7-Jan-20	17
9	101	c8	8-Jan-20	20
10	101	c9	9-Jan-20	2

The output must look like this.

1	subject	Asse visit	date	diameter	smallest
2	101	scr	1-Jan-20	20	20
3	101	c2	2-Jan-20	15	15
4	101	c3	3-Jan-20	10	10
5	101	c4	4-Jan-20	12	10
6	101	c5	5-Jan-20	9	9
7	101	c6	6-Jan-20	10	9
8	101	c7	7-Jan-20	17	9
9	101	c8	8-Jan-20	20	9
10	101	c9	9-Jan-20	2	2

I am not able to find a right approach, can anyone help.

Re: How to get smallest value in a variable based on their increasing order and dates

Ksharp — Wed, 29 Jul 2020 13:00:33 GMT

data have;
input 
_1
subject	$
Assevisit $
date $
diameter;
cards;
2
101
scr
1-Jan-20
20
3
101
c2
2-Jan-20
15
4
101
c3
3-Jan-20
10
5
101
c4
4-Jan-20
12
6
101
c5
5-Jan-20
9
7
101
c6
6-Jan-20
10
8
101
c7
7-Jan-20
17
9
101
c8
8-Jan-20
20
10
101
c9
9-Jan-20
2
;

data want;
 set have;
 retain min;
 min=min(min,diameter);
run;

Re: How to get smallest value in a variable based on their increasing order and dates

Tom — Wed, 29 Jul 2020 13:03:28 GMT

Create a new variable. Retain the value. Use the MIN() function.

data want;
  set have;
  by subject date ;
  if first.id then smallest=diameter;
  else smallest=min(smallest,diameter);
  retain smallest;
run;

Re: How to get smallest value in a variable based on their increasing order and dates

mkeintz — Thu, 06 Aug 2020 20:45:57 GMT

Editted note in italics:

@Ravindra_

You marked my submission as a solution, BUT IT IS WRONG. In fact, I don't remember submitting it - it looks like something I was investigating , and I must have (late at night) absentmindedly posted. I notice I didn't even add comments, which I usually do.

The core reason it is wrong is that the lag function is embedded inside the IFN function. In this case that means that the queue of values accessed by the lag function will never access the output of the IFN function (which was intended to update smallest).

A retain (as suggested by others) is essential:

Below is a corrected program:

data want;
  set have;
  by subject;
  retain smallest;
  smallest=ifn(first.subject,diameter,min(diameter,smallest));
run;

Below is the erroneous submission:

data want;
  set have;
  by subject ;
  smallest=ifn(first.subject,diameter,min(diameter,lag(smallest));
run;

Re: How to get smallest value in a variable based on their increasing order and dates

Ravindra_ — Thu, 06 Aug 2020 13:03:41 GMT

Thank you all for your response, i was able to release my report within timeline just because of your support. Many thanks for your help.