Solved
Contributor
Posts: 28

# To find a maximum value when a condition is met

Hi Friends,

Kindly resolve my query. I have attached a file for your reference.

I need to check the current maximum value when compared to previous day maximum value.

I am checking this on the variable, VS_Minus in the attached sheet and outputting to the variable

VS_Minus_Max .

But the logic should be, when dt(a variable in the sheet) .. When dt="Yes" then replace

VS_Minus_Max=VS_Minus and from next day, again compare the greater value from the

replaced previous value with the current VS_Minus values.

For example: n=82, dt="Yes", VS_Minus is 5.7875, then replaced vs_minus_max to 5.7875, then from n=83,

check max between 5.7875 and 5.8296(vs_minus) which is 5.8296, so need to replace vs_minus_max with this 5.8296 and n=84 max between 5.8296 and 5.902 which is 5.902..goes on like this until we encounter next dt="Yes"..

How to achieve this logic?please explain.

Accepted Solutions
Solution
‎08-11-2016 10:46 PM
Super User
Posts: 10,787

## Re: To find a maximum value when a condition is met

```You'd post your data at this forum. No one would like to download a file .
Assuming I understand what you are looking for.

data have;
infile cards truncover expandtabs;
input symbol :\$40. close	date1 :\$40.	close_flag \$	n	vs_minus_max	vs_minus	vs_plus_min	vs_plus	dt \$;
cards;
000001.SZ	6.82	2014/1/2	up	1
000001.SZ	6.65	2014/1/3	up	2
000001.SZ	6.5	2014/1/6	up	3
000001.SZ	6.48	2014/1/7	up	4
000001.SZ	6.55	2014/1/8	up	5
000001.SZ	6.59	2014/1/9	up	6
000001.SZ	6.59	2014/1/10	up	7
000001.SZ	6.47	2014/1/13	up	8
000001.SZ	6.53	2014/1/14	up	9
000001.SZ	6.5	2014/1/15	up	10
000001.SZ	6.52	2014/1/16	up	11
000001.SZ	6.4	2014/1/17	up	12
000001.SZ	6.3	2014/1/20	up	13
000001.SZ	6.33	2014/1/21	up	14
000001.SZ	6.57	2014/1/22	up	15
000001.SZ	6.52	2014/1/23	up	16
000001.SZ	6.5	2014/1/24	up	17
000001.SZ	6.35	2014/1/27	up	18
000001.SZ	6.41	2014/1/28	up	19
000001.SZ	6.44	2014/1/29	up	20
000001.SZ	6.35	2014/1/30	up	21
000001.SZ	6.34	2014/2/7	up	22
000001.SZ	6.45	2014/2/10	up	23
000001.SZ	6.69	2014/2/11	up	24
000001.SZ	6.65	2014/2/12	up	25
000001.SZ	6.81	2014/2/13	up	26
000001.SZ	6.73	2014/2/14	up	27
000001.SZ	6.67	2014/2/17	up	28
000001.SZ	6.5	2014/2/18	up	29
000001.SZ	6.69	2014/2/19	up	30
000001.SZ	6.55	2014/2/20	up	31
000001.SZ	6.47	2014/2/21	up	32
000001.SZ	6.22	2014/2/24	up	33
000001.SZ	6.14	2014/2/25	up	34
000001.SZ	6.19	2014/2/26	up	35
000001.SZ	6.26	2014/2/27	up	36
000001.SZ	6.2	2014/2/28	up	37
000001.SZ	6.15	2014/3/3	up	38
000001.SZ	6.12	2014/3/4	up	39
000001.SZ	5.96	2014/3/5	up	40
000001.SZ	6	2014/3/6	up	41
000001.SZ	6.02	2014/3/7	up	42
000001.SZ	5.75	2014/3/10	up	43
000001.SZ	5.71	2014/3/11	up	44
000001.SZ	5.77	2014/3/12	up	45
000001.SZ	5.89	2014/3/13	up	46
000001.SZ	5.75	2014/3/14	up	47
000001.SZ	5.84	2014/3/17	up	48
000001.SZ	5.79	2014/3/18	up	49
000001.SZ	5.71	2014/3/19	up	50	5.4367	5.4367	5.9833	5.9833
000001.SZ	5.63	2014/3/20	up	51	5.4367	5.3567	5.9033	5.9033
000001.SZ	6.01	2014/3/21	up	52	5.7289	5.7289	5.9033	6.2911
000001.SZ	6.01	2014/3/24	up	53	5.7322	5.7322	5.9033	6.2878
000001.SZ	5.91	2014/3/25	up	54	5.7322	5.6322	5.9033	6.1878
000001.SZ	5.96	2014/3/26	up	55	5.7322	5.6855	5.9033	6.2345
000001.SZ	6	2014/3/27	up	56	5.7322	5.7225	5.9033	6.2775
000001.SZ	6.01	2014/3/28	up	57	5.7325	5.7325	5.9033	6.2875
000001.SZ	6	2014/3/31	up	58	5.7325	5.7252	5.9033	6.2748
000001.SZ	6.02	2014/4/1	up	59	5.7476	5.7476	5.9033	6.2924
000001.SZ	6.08	2014/4/2	up	60	5.8058	5.8058	5.9033	6.3542
000001.SZ	6	2014/4/3	up	61	5.8058	5.7255	5.9033	6.2745
000001.SZ	6.01	2014/4/4	up	62	5.8058	5.7358	5.9033	6.2842
000001.SZ	6.29	2014/4/8	up	63	6.008	6.008	5.9033	6.572
000001.SZ	6.24	2014/4/9	up	64	6.008	5.9589	5.9033	6.5211
000001.SZ	6.33	2014/4/10	up	65	6.0498	6.0498	5.9033	6.6102
000001.SZ	6.35	2014/4/11	up	66	6.068	6.068	5.9033	6.632
000001.SZ	6.29	2014/4/14	up	67	6.068	6.0077	5.9033	6.5723
000001.SZ	6.11	2014/4/15	up	68	6.068	5.8265	5.9033	6.3935
000001.SZ	6.13	2014/4/16	up	69	6.068	5.8456	5.9033	6.4144
000001.SZ	6.08	2014/4/17	up	70	6.068	5.7938	5.9033	6.3662
000001.SZ	6.02	2014/4/18	up	71	5.7335	5.7335	5.9033	6.3065	Yes
000001.SZ	5.96	2014/4/21	up	72	5.6744	5.6744	5.9033	6.2456	Yes
000001.SZ	6.16	2014/4/22	up	73	6.068	5.8699	5.9033	6.4501
000001.SZ	6.3	2014/4/23	up	74	6.068	6.0183	5.9033	6.5817
000001.SZ	6.26	2014/4/24	up	75	6.068	5.9789	5.9033	6.5411
000001.SZ	6.27	2014/4/25	up	76	6.068	5.9937	5.9033	6.5463
000001.SZ	6.15	2014/4/28	up	77	6.068	5.8725	5.9033	6.4275
000001.SZ	6.22	2014/4/29	up	78	6.068	5.9419	5.9033	6.4981
000001.SZ	6.21	2014/4/30	up	79	6.068	5.9355	5.9033	6.4845
000001.SZ	6.13	2014/5/5	up	80	6.068	5.8591	5.9033	6.4009
000001.SZ	6.1	2014/5/6	up	81	6.068	5.8345	5.9033	6.3655
000001.SZ	6.05	2014/5/7	up	82	5.7875	5.7875	5.9033	6.3125	Yes
000001.SZ	6.09	2014/5/8	up	83	6.068	5.8296	5.9033	6.3504
000001.SZ	6.16	2014/5/9	up	84	6.068	5.902	5.9033	6.418
000001.SZ	6.32	2014/5/12	up	85	6.068	6.0593	5.9033	6.5807
000001.SZ	6.26	2014/5/13	up	86	6.068	6.0023	5.9033	6.5177
000001.SZ	6.3	2014/5/14	up	87	6.068	6.0444	5.9033	6.5556
000001.SZ	6.24	2014/5/15	up	88	6.068	5.9841	5.9033	6.4959
000001.SZ	6.28	2014/5/16	up	89	6.068	6.0253	5.9033	6.5347
000001.SZ	6.28	2014/5/19	up	90	6.068	6.0289	5.9033	6.5311
000001.SZ	6.26	2014/5/20	up	91	6.068	6.0119	5.9033	6.5081
000001.SZ	6.31	2014/5/21	up	92	6.068	6.061	5.9033	6.559
000001.SZ	6.35	2014/5/22	up	93	6.104	6.104	5.9033	6.596
000001.SZ	6.45	2014/5/23	up	94	6.2046	6.2046	5.9033	6.6954
000001.SZ	6.45	2014/5/26	up	95	6.2067	6.2067	5.9033	6.6933
000001.SZ	6.45	2014/5/27	up	96	6.2106	6.2106	5.9033	6.6894
000001.SZ	6.47	2014/5/28	up	97	6.2318	6.2318	5.9033	6.7082
000001.SZ	6.41	2014/5/29	up	98	6.2318	6.1706	5.9033	6.6494
000001.SZ	6.41	2014/5/30	up	99	6.2318	6.1709	5.9033	6.6491
000001.SZ	6.42	2014/6/3	up	100	6.2318	6.1815	5.9033	6.6585
000001.SZ	6.31	2014/6/4	up	101	6.2318	6.0712	5.9033	6.5488
000001.SZ	6.37	2014/6/5	up	102	6.2318	6.1438	5.9033	6.5962
000001.SZ	6.34	2014/6/6	up	103	6.2318	6.1156	5.9033	6.5644
000001.SZ	6.4	2014/6/9	up	104	6.2318	6.1759	5.9033	6.6241
000001.SZ	6.55	2014/6/10	up	105	6.3247	6.3247	5.9033	6.7753
000001.SZ	6.57	2014/6/11	up	106	6.3498	6.3498	5.9033	6.7902
000001.SZ	6.59	2014/6/12	up	107	6.371	6.371	5.9033	6.809
000001.SZ	6.86	2014/6/13	up	108	6.6314	6.6314	5.9033	7.0886
000001.SZ	6.87	2014/6/16	up	109	6.6402	6.6402	5.9033	7.0998
000001.SZ	6.81	2014/6/17	up	110	6.6402	6.5811	5.9033	7.0389
000001.SZ	6.69	2014/6/18	up	111	6.6402	6.4602	5.9033	6.9198
000001.SZ	6.63	2014/6/19	up	112	6.399	6.399	5.9033	6.861	Yes
000001.SZ	6.68	2014/6/20	up	113	6.6402	6.4586	5.9033	6.9014
000001.SZ	6.63	2014/6/23	up	114	6.408	6.408	5.9033	6.852	Yes
000001.SZ	6.65	2014/6/24	up	115	6.6402	6.4337	5.9033	6.8663
000001.SZ	6.63	2014/6/25	up	116	6.4146	6.4146	5.9033	6.8454	Yes
;
run;
data want;
set have;
retain max;
if dt='Yes' then do;max=vs_minus;vs_minus_max=vs_minus;end;
max=max(max,vs_minus);
vs_minus_max=max;
run;

```

All Replies
Super User
Posts: 9,599

## Re: To find a maximum value when a condition is met

Hi,

Sorry, I would not download Excel files.  Please post test data - in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

And what the output should look like.  You should be able to either merge data to get previous values or max, or you may be able to use lag() function.

Contributor
Posts: 28

## Re: To find a maximum value when a condition is met

Thaks for your response. I am not aware of that.

Apologise!!!

I am going to check maximum value when compared to previous value and current value and retain it, also need to do all this only when a flag=Yes and until when a condition close < Volatility stop is met.

I got a reply from one of the blog friend, I will try based on his advise.

Thanks!!!

Solution
‎08-11-2016 10:46 PM
Super User
Posts: 10,787

## Re: To find a maximum value when a condition is met

```You'd post your data at this forum. No one would like to download a file .
Assuming I understand what you are looking for.

data have;
infile cards truncover expandtabs;
input symbol :\$40. close	date1 :\$40.	close_flag \$	n	vs_minus_max	vs_minus	vs_plus_min	vs_plus	dt \$;
cards;
000001.SZ	6.82	2014/1/2	up	1
000001.SZ	6.65	2014/1/3	up	2
000001.SZ	6.5	2014/1/6	up	3
000001.SZ	6.48	2014/1/7	up	4
000001.SZ	6.55	2014/1/8	up	5
000001.SZ	6.59	2014/1/9	up	6
000001.SZ	6.59	2014/1/10	up	7
000001.SZ	6.47	2014/1/13	up	8
000001.SZ	6.53	2014/1/14	up	9
000001.SZ	6.5	2014/1/15	up	10
000001.SZ	6.52	2014/1/16	up	11
000001.SZ	6.4	2014/1/17	up	12
000001.SZ	6.3	2014/1/20	up	13
000001.SZ	6.33	2014/1/21	up	14
000001.SZ	6.57	2014/1/22	up	15
000001.SZ	6.52	2014/1/23	up	16
000001.SZ	6.5	2014/1/24	up	17
000001.SZ	6.35	2014/1/27	up	18
000001.SZ	6.41	2014/1/28	up	19
000001.SZ	6.44	2014/1/29	up	20
000001.SZ	6.35	2014/1/30	up	21
000001.SZ	6.34	2014/2/7	up	22
000001.SZ	6.45	2014/2/10	up	23
000001.SZ	6.69	2014/2/11	up	24
000001.SZ	6.65	2014/2/12	up	25
000001.SZ	6.81	2014/2/13	up	26
000001.SZ	6.73	2014/2/14	up	27
000001.SZ	6.67	2014/2/17	up	28
000001.SZ	6.5	2014/2/18	up	29
000001.SZ	6.69	2014/2/19	up	30
000001.SZ	6.55	2014/2/20	up	31
000001.SZ	6.47	2014/2/21	up	32
000001.SZ	6.22	2014/2/24	up	33
000001.SZ	6.14	2014/2/25	up	34
000001.SZ	6.19	2014/2/26	up	35
000001.SZ	6.26	2014/2/27	up	36
000001.SZ	6.2	2014/2/28	up	37
000001.SZ	6.15	2014/3/3	up	38
000001.SZ	6.12	2014/3/4	up	39
000001.SZ	5.96	2014/3/5	up	40
000001.SZ	6	2014/3/6	up	41
000001.SZ	6.02	2014/3/7	up	42
000001.SZ	5.75	2014/3/10	up	43
000001.SZ	5.71	2014/3/11	up	44
000001.SZ	5.77	2014/3/12	up	45
000001.SZ	5.89	2014/3/13	up	46
000001.SZ	5.75	2014/3/14	up	47
000001.SZ	5.84	2014/3/17	up	48
000001.SZ	5.79	2014/3/18	up	49
000001.SZ	5.71	2014/3/19	up	50	5.4367	5.4367	5.9833	5.9833
000001.SZ	5.63	2014/3/20	up	51	5.4367	5.3567	5.9033	5.9033
000001.SZ	6.01	2014/3/21	up	52	5.7289	5.7289	5.9033	6.2911
000001.SZ	6.01	2014/3/24	up	53	5.7322	5.7322	5.9033	6.2878
000001.SZ	5.91	2014/3/25	up	54	5.7322	5.6322	5.9033	6.1878
000001.SZ	5.96	2014/3/26	up	55	5.7322	5.6855	5.9033	6.2345
000001.SZ	6	2014/3/27	up	56	5.7322	5.7225	5.9033	6.2775
000001.SZ	6.01	2014/3/28	up	57	5.7325	5.7325	5.9033	6.2875
000001.SZ	6	2014/3/31	up	58	5.7325	5.7252	5.9033	6.2748
000001.SZ	6.02	2014/4/1	up	59	5.7476	5.7476	5.9033	6.2924
000001.SZ	6.08	2014/4/2	up	60	5.8058	5.8058	5.9033	6.3542
000001.SZ	6	2014/4/3	up	61	5.8058	5.7255	5.9033	6.2745
000001.SZ	6.01	2014/4/4	up	62	5.8058	5.7358	5.9033	6.2842
000001.SZ	6.29	2014/4/8	up	63	6.008	6.008	5.9033	6.572
000001.SZ	6.24	2014/4/9	up	64	6.008	5.9589	5.9033	6.5211
000001.SZ	6.33	2014/4/10	up	65	6.0498	6.0498	5.9033	6.6102
000001.SZ	6.35	2014/4/11	up	66	6.068	6.068	5.9033	6.632
000001.SZ	6.29	2014/4/14	up	67	6.068	6.0077	5.9033	6.5723
000001.SZ	6.11	2014/4/15	up	68	6.068	5.8265	5.9033	6.3935
000001.SZ	6.13	2014/4/16	up	69	6.068	5.8456	5.9033	6.4144
000001.SZ	6.08	2014/4/17	up	70	6.068	5.7938	5.9033	6.3662
000001.SZ	6.02	2014/4/18	up	71	5.7335	5.7335	5.9033	6.3065	Yes
000001.SZ	5.96	2014/4/21	up	72	5.6744	5.6744	5.9033	6.2456	Yes
000001.SZ	6.16	2014/4/22	up	73	6.068	5.8699	5.9033	6.4501
000001.SZ	6.3	2014/4/23	up	74	6.068	6.0183	5.9033	6.5817
000001.SZ	6.26	2014/4/24	up	75	6.068	5.9789	5.9033	6.5411
000001.SZ	6.27	2014/4/25	up	76	6.068	5.9937	5.9033	6.5463
000001.SZ	6.15	2014/4/28	up	77	6.068	5.8725	5.9033	6.4275
000001.SZ	6.22	2014/4/29	up	78	6.068	5.9419	5.9033	6.4981
000001.SZ	6.21	2014/4/30	up	79	6.068	5.9355	5.9033	6.4845
000001.SZ	6.13	2014/5/5	up	80	6.068	5.8591	5.9033	6.4009
000001.SZ	6.1	2014/5/6	up	81	6.068	5.8345	5.9033	6.3655
000001.SZ	6.05	2014/5/7	up	82	5.7875	5.7875	5.9033	6.3125	Yes
000001.SZ	6.09	2014/5/8	up	83	6.068	5.8296	5.9033	6.3504
000001.SZ	6.16	2014/5/9	up	84	6.068	5.902	5.9033	6.418
000001.SZ	6.32	2014/5/12	up	85	6.068	6.0593	5.9033	6.5807
000001.SZ	6.26	2014/5/13	up	86	6.068	6.0023	5.9033	6.5177
000001.SZ	6.3	2014/5/14	up	87	6.068	6.0444	5.9033	6.5556
000001.SZ	6.24	2014/5/15	up	88	6.068	5.9841	5.9033	6.4959
000001.SZ	6.28	2014/5/16	up	89	6.068	6.0253	5.9033	6.5347
000001.SZ	6.28	2014/5/19	up	90	6.068	6.0289	5.9033	6.5311
000001.SZ	6.26	2014/5/20	up	91	6.068	6.0119	5.9033	6.5081
000001.SZ	6.31	2014/5/21	up	92	6.068	6.061	5.9033	6.559
000001.SZ	6.35	2014/5/22	up	93	6.104	6.104	5.9033	6.596
000001.SZ	6.45	2014/5/23	up	94	6.2046	6.2046	5.9033	6.6954
000001.SZ	6.45	2014/5/26	up	95	6.2067	6.2067	5.9033	6.6933
000001.SZ	6.45	2014/5/27	up	96	6.2106	6.2106	5.9033	6.6894
000001.SZ	6.47	2014/5/28	up	97	6.2318	6.2318	5.9033	6.7082
000001.SZ	6.41	2014/5/29	up	98	6.2318	6.1706	5.9033	6.6494
000001.SZ	6.41	2014/5/30	up	99	6.2318	6.1709	5.9033	6.6491
000001.SZ	6.42	2014/6/3	up	100	6.2318	6.1815	5.9033	6.6585
000001.SZ	6.31	2014/6/4	up	101	6.2318	6.0712	5.9033	6.5488
000001.SZ	6.37	2014/6/5	up	102	6.2318	6.1438	5.9033	6.5962
000001.SZ	6.34	2014/6/6	up	103	6.2318	6.1156	5.9033	6.5644
000001.SZ	6.4	2014/6/9	up	104	6.2318	6.1759	5.9033	6.6241
000001.SZ	6.55	2014/6/10	up	105	6.3247	6.3247	5.9033	6.7753
000001.SZ	6.57	2014/6/11	up	106	6.3498	6.3498	5.9033	6.7902
000001.SZ	6.59	2014/6/12	up	107	6.371	6.371	5.9033	6.809
000001.SZ	6.86	2014/6/13	up	108	6.6314	6.6314	5.9033	7.0886
000001.SZ	6.87	2014/6/16	up	109	6.6402	6.6402	5.9033	7.0998
000001.SZ	6.81	2014/6/17	up	110	6.6402	6.5811	5.9033	7.0389
000001.SZ	6.69	2014/6/18	up	111	6.6402	6.4602	5.9033	6.9198
000001.SZ	6.63	2014/6/19	up	112	6.399	6.399	5.9033	6.861	Yes
000001.SZ	6.68	2014/6/20	up	113	6.6402	6.4586	5.9033	6.9014
000001.SZ	6.63	2014/6/23	up	114	6.408	6.408	5.9033	6.852	Yes
000001.SZ	6.65	2014/6/24	up	115	6.6402	6.4337	5.9033	6.8663
000001.SZ	6.63	2014/6/25	up	116	6.4146	6.4146	5.9033	6.8454	Yes
;
run;
data want;
set have;
retain max;
if dt='Yes' then do;max=vs_minus;vs_minus_max=vs_minus;end;
max=max(max,vs_minus);
vs_minus_max=max;
run;

```
Contributor
Posts: 28

## Re: To find a maximum value when a condition is met

Apologise. I am not aware of that.

I will come back if resolved or if any other issues.

Thanks much!!!

Contributor
Posts: 28

## Re: To find a maximum value when a condition is met

Thanks much Xia Keshan (Ksharp)!!! You have always given me best and accurate solution to my logic issues.

Your logic is working fine with very little modification as according to our data.

Thanks a lot!!!

☑ This topic is solved.