reshape data

[saseles]

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" ....?

[novinosrin]

@saseles All you need to do is add this statement in your last step like

year=compress(year,' ','a');

data final_want;
merge want(in=a)  temp(in=b);
by  year from to;
if a;
if a and b then Number=1;
else NUmber=0;
year=compress(year,' ','a');
run;

[saseles]

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.

[s_lassen]

@saseles: It seemed from your initial specification that you did not want the a to a, b to b and c to c connection counted as moves. But if you want those too, just get rid of the code to remove them:

data moves;
  set have;
  array cities(2000:2002) city2000-city2002;
  do year=2001 to 2002;
      from=cities(year-1);
      to=cities(year);
      output;
    end;
  keep from to year;
run;
proc freq data=moves noprint;
  tables year*from*to/sparse out=want(rename=(count=number) drop=percent);
run;

This seems to be the same as your WANT data, except that yours are not sorted.

[saseles]

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?

[s_lassen]

That's probably because you have some observations in your real data with one or more variables missing. If you do not want to see those, you can drop them when creating the MOVES table:

data moves;
  set have;
  array cities(2000:2002) city2000-city2002;
  do year=2001 to 2002;
      from=cities(year-1);
      to=cities(year);
      if from ne ' ' and to ne ' ' then 
        output;
    end;
  keep from to year;
run;