DATA Step, Macro, Functions and more

To find a maximum value when a condition is met

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

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.

 

Thanks in advance!!!


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

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;


View solution in original post


All Replies
Super User
Super User
Posts: 7,963

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,035

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.

Thanks for your reply. I will try based on your logic.

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 458 views
  • 1 like
  • 3 in conversation