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" ....?
@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;
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.
@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.
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?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.