Hi again draycut, it was my mistake,,, all good. thanks again ... View more

Hi draycut, Thanks for the code. It works well. However, not for my actual data… The IDs in my actual data are not ordered as 1,2,3..that I showed here. They are 8 digit randomised numbers like 30149326,12365478 etc.. Is it the issue does not let the code work well? Appreciate your time ... View more

Thanks a lot. I really appreciate your time ... View more

Hi @novinosrin , Thanks for your reply. My dataset is quiet big and I have more than 30 variables.  To create a want variable I need to use V1, v2 and V3.   data have; input V1 V2 v3 V4 V5……want ; cards; 1 1 1  2  3 ….10 1 1 1  3  3…..10 1 1 1  4  5……10 1 1 2  5  6……10 1 2 6  4  4…….9 1 2 3  4  7…….9 1 2 6  5  4…….9 1 2 4  5  5…….9 1 2 4  8  0…….9 2 2 4  4   3……8 2 2 4  2  5…….8 2 2 8  4   3……8 2 2 8   2  5…….8 2 5 9   4   3……16 2 5 9   2  5…….16 2 5 9   4   3……16 2 5 9   2  5…….16 ;   10=( 3**2)+ (1**2) where 3 is the frequency of 1 in V3 and 1 is the frequency of 2 in V3 9= (2**2) +(2**2)+(1**2) where 2 is the frequency of 6 in V3 and 2 (in the second Perrantes) is the frequency of 4 in V3 and 1 is the frequency of 3 8= (2**2) +(2**2) where 2 is the frequency of 4 in V3 and 2 (in the second Perrantes) is the frequency of 8 in V3 8= (2**2) +(2**2) where 2 is the frequency of 4 in V3 and 2 (in the second Perrantes) is the frequency of 8 in V3 16= (4**2) where 4 is the frequency of 9 in V3   I need to create the want variable and merge it to the original dataset  like what I showed.   Thank you ... View more

Hi Novinosrin, A quick question. I appreciate your help in advance. I have played around with the code. However, when we have other variables ( V4, V5 etc), the results change like below: data have; input V1 V2 v3 V4 V5 ; cards; 1 1 1 2 3 1 1 1 3 3 1 1 1 4 5 1 1 2 5 6 1 2 6 4 4 1 2 3 4 7 1 2 3 5 4 1 2 4 1 2 4 1 2 4 ; Instead of having 10 and 14 like before we have 28 and 36 because the sum function sums up all values created in want. Maybe we need a different grouping statement. But I don't know how? ... View more

Hi Novinosrin, I really apprecite your time..Was very helpful!! ... View more

Thank you Maybe because I corrected this in one of my posts.. ... View more

I really appreciate it! ... View more

Thank you PGStats for your reply and help. Yes, I need the table for the next analysis. ... View more

Thank you very much Novinosrin. I really appreciate your help. The code works well. Could you please clarify it for me. really like how it woks and learn... Thanks again ... View more

Sorry to get Square I should have used **. Yes I want to create a table using SQL in SAS ... View more

we need to first look at V2. for each distinct value say 1 we count the distinct values of V3. When V2 =1 , V3 has 1 (frequency =3) and 2(frequency =1). then we need to obtain the square of each frequency that means 3*2 and 1*2 and sum up the results that means 9+1= 10 Hope it is clear Thanks ... View more

Thanks for your reply: Here is the data and expected output. data have; input V1 V2 v3 want; cards; 1 1 1 10 1 1 1 10 1 1 1 10 1 1 2 10 1 2 6 14 1 2 3 14 1 2 3 14 1 2 4 14 1 2 4 14 1 2 4 14 ;;;; run; 10=( 3*2)+ (1*2) where 3 is the frequency of 1 in V3 and 1 is the frequency of 2 in V3 14= (1*2) +(2*2)+(3*2) where 1 is the frequency of 6 in V3 and 2 is the frequency of 3 in V3 and 3 is the frequency of 4 ... View more