BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lakshmiG
Obsidian | Level 7

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!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

lakshmiG
Obsidian | Level 7

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!!!

Ksharp
Super User
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;


lakshmiG
Obsidian | Level 7

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!!!

lakshmiG
Obsidian | Level 7

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!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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