Thanks for the quick reply. I am away from my computer now. I will test shortly. But i think there may be problems if year is defined both char and num. ... View more

Thanks a lot for the code. It get my expected result, but the year is in the format of "city2001" ... any way to modify the code to get year as "2001" ....? ... View more

Yes, i need the pairs to itself now. But the code always generate some empty obs for some "to" location. example   year        from      to     number 2001          a                     0   Any ideas? ... View more

Hello s_lassen:   Thanks for your reply. But i am looking for the count of numbers of movement. For example: data have; input city2000 $ city2001 $ city2002 $; datalines; a b b a b a b a a c c c c a a ; data want; input from $ to $ year number_of_moves; datalines; a a 2001 0 a b 2001 2 a c 2001 0 b b 2001 0 b a 2001 1 b c 2001 0 c c 2001 1 c a 2001 1 c b 2001 0 a a 2002 2 a b 2002 0 a c 2002 0 b b 2002 1 b a 2002 1 b c 2002 0 c c 2002 1 c a 2002 0 c b 2002 0 ; ... View more

To make my question clear, i am looking for the number of movement between all possible city pairs including city to itself. My previous testing data was not that good. So if you use this one: data have; input city2000 $ city2001 $ city2002 $; datalines; a b b a b a b a a c c c c a a ; I am looking the result as below: data want; input from $ to $ year number_of_moves; datalines; a a 2001 0 a b 2001 2 a c 2001 0 b b 2001 0 b a 2001 1 b c 2001 0 c c 2001 1 c a 2001 1 c b 2001 0 a a 2002 2 a b 2002 0 a c 2002 0 b b 2002 1 b a 2002 1 b c 2002 0 c c 2002 1 c a 2002 0 c b 2002 0 ; Thanks again for your time! ... View more

I guess that my question may be not clear. I am going to track the numbers of movement which should be sum of movements.   e.g. 3 people lived in city a in 2000 and they moved to city b in 2001, the number is 3 from a to b in 2001.    Also, i would like to add the number of people who stay where they were, e.g. 4 people lived in city a in a given year and the next year, so the number of city pair " from a to a" is 4.      ... View more

No worries, thanks a lot! Talk to you tomorrow. ... View more

I think there are something wrong with the code. I didn't pay attention to the testing data.    The result is that the number is always 0  or 1. If you use the following testing data, you will still get number is o or 1. Even there should be two moved from a to b from 2000 to 2001. data have; input city2000 $ city2001 $ city2002 $ city2003 $; datalines; a b b c a b a a b a a a c c c c c a a a ; ... View more

Oh, sorry, ignore my previous post. I found a typo in my code from adding my own library path. Give it a new try. finger crossed! ... View more

Emmmm... not sure what happened....   It works in my short testing data. But not working on real data. error: Ambiguous reference, column year is in more than one table ...   I compared the two results. It seems working on my real data until the last proc sql. The data want looks good. But i got the above error from proc sql, and there is no final_want generated. ... View more

Trying now!  ... View more

where is the data "t" ? Do i need to run some other codes? ... View more

I tried again. Same thing  ... View more

I tried using ".... 'mydata.temp' ....", but I got error that "Hash object added XXXXXX items when memory failure occurred" "Insufficient memory to execute data step ...."   Is something that related to my computer, not the code? I notice that the temp data has really large size. It is much larger then my original data.  ... View more

Thanks a lot! i am almost there! The problem is that I have large data that cannot use the SAS work library. So, my data is save in my own defined library, say "mydata.have". I modified your code to direct all temp data to my library. But i don't know how to handle the third line in "data want" statement. i.e. "dcl hash H1 (dataset:'temp',multidata:'y') ;" . How to modify the one " ...dataset:'temp' ..." ? I hope this is the final step. Thanks again! ... View more