Hi @yabwon Thankyou for your reply. Yes its perfect and that's what i was looking for. Really appreciate. ... View more

Thankyou @maguiremq its almost there and i am getting the results as expected with one last thing that in your answer if the code in ('A1','B1') but here i am looking for all the codes between A1-A7 and B1-B7. Since the code is doing seq increment on the basis of previous code not matching. So if i replace the code with below snippet i am only getting one record for the id = 5 whereas it should have all 6 records since the code for that id has A1-A3 which can be upto A7. Apologies if I haven't explained it earlier. if code in ('A1','A2','A3','A4','A5','A6','A7','B1','B2','B3','B4','B5','B6','B7') then flag = 1;   ... View more

Yes in the chronologically order. Meaning if i have any code other than A1-A7 and B1-B7 in between any 6 month cycle. Then only keep the observations per group for the latest months . ... View more

Thankyou for your reply @PaigeMiller . i have tried to add more explanation to the above. ... View more

Thankyou for your reply @maguiremq .Yes the additional patterns can be between (A1 to A7) and (B1 to B7) and i need all of them except if they break in between for example in ID = 1 it had a different code (Z1 & X1) in Sep and Oct in between and i dont need the observations prior to that even if its one of the codes i am looking for. So i cant just simply filter out on 'B1' as a group can have all A1 or B1 codes and that ways i will have extra observations not required. Please see the code i have added 2 more ids to explain. data have; infile datalines delimiter = ' '; input id date :date9. code :$2. flag cnt; format date date9.; datalines; 1 30NOV2021 B1 1 1 1 31OCT2021 X1 0 0 1 30SEP2021 Z1 0 0 1 31AUG2021 A1 1 1 1 31JUL2021 A1 1 2 1 30JUN2021 A1 1 3 2 30NOV2021 B1 1 1 2 31OCT2021 B1 1 2 2 30SEP2021 B1 1 3 2 31AUG2021 Z1 0 0 2 31JUL2021 A1 1 1 2 30JUN2021 A1 1 2 3 30NOV2021 B1 1 1 3 31OCT2021 B1 1 2 3 30SEP2021 Z1 0 0 3 31AUG2021 Z1 0 0 3 31JUL2021 A1 1 1 3 30JUN2021 A1 1 2 4 30NOV2021 B1 1 1 4 31OCT2021 B1 1 2 4 30SEP2021 B1 1 3 4 31AUG2021 B1 1 4 4 31JUL2021 B1 1 5 4 30JUN2021 B1 1 6 5 30NOV2021 A3 1 1 5 31OCT2021 A2 1 2 5 30SEP2021 A1 1 3 5 31AUG2021 A1 1 4 5 31JUL2021 A1 1 5 5 30JUN2021 A1 1 6 ; run;   If i just filter on the required codes will give extra observations and i need only the ones highlighted. id date code flag cnt 1 30Nov2021 B1 1 1 1 31Aug2021 A1 1 1 1 31Jul2021 A1 1 2 1 30Jun2021 A1 1 3 2 30Nov2021 B1 1 1 2 31Oct2021 B1 1 2 2 30Sep2021 B1 1 3 2 31Jul2021 A1 1 1 2 30Jun2021 A1 1 2 3 30Nov2021 B1 1 1 3 31Oct2021 B1 1 2 3 31Jul2021 A1 1 1 3 30Jun2021 A1 1 2 ... View more

i was able to create a flag and counter (cnt) for the observations but i need only the ones in green. ... View more

Thanks a lot Kurt. its perfect but i also want to filter only group le 2 (having ABC + any other group value) AND group gt 2 (having ABC + any other group value) per ID. Right now group le 2 also has combination of DEF & GHI. Really appreciate. ... View more

Hi Paige, Thanks for your reply. Really appreciate. I need to count the number of ids and sum the values based on a combination of group. So scenario 1, let say group value ABC with only 1 other group value (any other than ABC) and count the ids and sum the variable. such cases. scenario 2 - Then group value ABC with more than 1 other group value (any other than ABC) and count the ids and sum the variable. So the output should be like ; scenario 1 Group id count of group count of account sum_var ABC 5678 2 2 107985447.5 JKL 5678 2 2 107985447.5 scenario 2 Group id count of group count of account sum_var ABC 1234 3 4 171119250.5 DEF 1234 3 4 171119250.5 DEF 1234 3 4 171119250.5 GHI 1234 3 4 171119250.5 Also, ids that have been summarized in scenario 1 should not be counted in scenario 2. Thanks in advance ... View more

Hi Tom, Thanks for your reply. Really appreciate. I need to count the number of ids and sum the values based on a combination of group. So scenario 1, let say group value ABC with only 1 other group value (any other than ABC) and count the ids and sum the variable. such cases. scenario 2 - Then group value ABC with more than 1 other group value (any other than ABC) and count the ids and sum the variable. So the output should be like ; scenario 1 Group id count of group count of account sum_var ABC 5678 2 2 107985447.5 JKL 5678 2 2 107985447.5 scenario 2 Group id count of group count of account sum_var ABC 1234 3 4 171119250.5 DEF 1234 3 4 171119250.5 DEF 1234 3 4 171119250.5 GHI 1234 3 4 171119250.5 Thanks in advance ... View more

Also to add, ids that have been summarized in scenario 1 should not be counted in scenario 2. ... View more

Hi Kurt, Thanks for your reply. Really appreciate. Actually you are correct. May be I was not clear in earlier. I need to count the number of ids and sum the values based on a combination of group. So scenario 1, let say group value ABC with only 1 other group value (any other than ABC) and count the ids and sum the variable. such cases. scenario 2 - Then group value ABC with more than 1 other group value (any other than ABC) and count the ids and sum the variable. So the output should be like ; scenario 1 Group id count of group count of account sum_var ABC 5678 2 2 107985447.5 JKL 5678 2 2 107985447.5 scenario 2 Group id count of group count of account sum_var ABC 1234 3 4 171119250.5 DEF 1234 3 4 171119250.5 DEF 1234 3 4 171119250.5 GHI 1234 3 4 171119250.5 Thanks in advance ... View more

Hi Paige, Thanks for your reply. The data set is big. Please find below an example of the same. There are 9 such groups in the actual data and in the example below i have taken only 5. So, if you see id 1234 is present in three different groups. I want to count in how many other group that id is other then ABC with that group name and also sum the field sum_Var? Group Account sum_var id ABC 10792402 53475319.06 1234 DEF 3928847912 4510128.39 1234 DEF 4682433039 89231128.39 1234 GHI 1737576722 23902674.65 1234 ABC 43169608 53475319.06 5678 JKL 15715391648 54510128.39 5678 ... View more