reshape data

[novinosrin]

Please follow the script carefully and diligently and the only script above (the latest)

t is from this step

proc transpose data=have out=t(keep=col1);
by city: notsorted;
var city:;
run;

proc sort data=t nodupkey;
by col1;
run;

[saseles]

Trying now! 

[saseles]

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.

[saseles]

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!

[novinosrin]

Or  another option without sql using merge, notice the steps after temp and want

data have;
input city2000 $ city2001 $ city2002 $ city2003 $;
datalines;
a a b c
a a a a
b a a a
c c c c
c a a a
;

data temp;
set have end=last;
array t(*) city:;
do _n_= 2 to dim(t);
year=vname(t(_n_));
from=t(_n_-1);
to=t(_n_);
output;
end;
drop city:;
run;
proc transpose data=have out=t(keep=col1);
by city: notsorted;
var city:;
run;

proc sort data=t nodupkey;
by col1;
run;

data want;
if 0 then set have;
array t(*) city:;
set t(rename=(col1=from));
do _n_= 2 to dim(t);
year=vname(t(_n_));
do n=1 to nobs ;
set t(rename=(col1=to)) nobs=nobs point=n;
if to ne from then output;
end;
end;
keep from year to ;
run;


proc sort data=want;
by  year from to;;
run;
proc sort data=temp;
by  year from to;
run;



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;
run;

[saseles]

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. 

[novinosrin]

@saseles Just got home. I'm afraid I don't have SAS software at home, otherwise I would deep dive right away. Anyway, review the data and requirement thoroughly. Saying  that I'm known for carelessness and lack of attention to detail. So, i'll try to pay serious attention to the requirement at length tomorrow if nobody answered by then. 

The beauty of this forum is 24//7. lol

[saseles]

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
;

[novinosrin]

@saseles @If it's not very urgent, I will take a look when i get back to my college lab tomorrow morning chicago time. It's a bit too late here and I am afraid i need to catch the train to go home. I promise I will have a go at it. Nonetheless I am sure, somebody might give you the much needed answer while I am asleep as the night moves on here

[saseles]

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

[saseles]

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!

[s_lassen]

Here is a possible solution, which uses PROC FREQ with the SPARSE option (to get all possible combinations):

data moves;
  set have;
  array cities(2000:2003) city2000-city2003;
  do year=2001 to 2003;
	from=cities(year-1);
	to=cities(year);
	if from ne to then 
	  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; 

A lot less complicated than the hash stuff.

[saseles]

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
;

[novinosrin]

Good morning @saseles  I kinda expected that requirement

[novinosrin]

data have;
input city2000 $ city2001 $ city2002 $;
datalines;
a b b
a b a
b a a
c c c
c a a
;


data temp;
set have end=last;
array t(*) city:;
do _n_= 2 to dim(t);
year=vname(t(_n_));
from=t(_n_-1);
to=t(_n_);
output;
end;
drop city:;
run;

proc sql;
  create table temp2 as
    select year, from, to, count(*) as number_of_moves
      from temp
        group by year, from, to;
		  
quit;
proc transpose data=have out=t(keep=col1);
by city: notsorted;
var city:;
run;

proc sort data=t nodupkey;
by col1;
run;


data temp3;
if 0 then set have;
array t(*) city:;
set t(rename=(col1=from));
do _n_= 2 to dim(t);
year=vname(t(_n_));
do n=1 to nobs ;
set t(rename=(col1=to)) nobs=nobs point=n;
number_of_moves=0;
output;
end;
end;
keep from year to number_of_moves ;
run;

proc sort data=temp3;
by  year from to;;
run;
proc sort data=temp2;
by  year from to;
run;

data final_want;
merge temp3(in=a)  temp2(in=b);
by  year from to;
if a;
run;