topic Re: reshape data in SAS Programming

reshape data

saseles — Mon, 11 Jun 2018 21:29:53 GMT

Hello:

I have a data set as following: (real data have more years and locations)

 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
;

I would like to have a data which tracks the movement between all possible pairs of cities by year. So, the result will be alike below. Note that it doesn't include the city pair of itself, but if i want to include pairs like a - a, b - c, c - c. Is that easy to modify code?

 data want;
input from $ to $ year number;
datalines;
a b 2001 0
a c 2001 0
b a 2001 1
b c 2001 0
c a 2001 1
c b 2001 0
a b 2002 1
a c 2002 0
b a 2002 0
b c 2002 0
c a 2002 0
c b 2002 0
a b 2003 0
a c 2003 0
b a 2003 0
b c 2003 1
c a 2003 0
c b 2003 0
;

Thanks for any help!

Re: reshape data

novinosrin — Mon, 11 Jun 2018 22:06:46 GMT

Hi @saseles Your requirement seems very similar to this thread

https://communities.sas.com/t5/Base-SAS-Programming/construct-my-own-data-sets-from-two-source-data-sets/m-p/468954#M119846

Can you please review and see if you are able to modify to your needs?

Re: reshape data

saseles — Mon, 11 Jun 2018 22:16:21 GMT

Hello novinosrin:

I can tell the similarity. But i am don't understand the data old_2. Seems that i only have old_1. I don't know how to modify the code since there are some commend are really new to me.

Any suggestions?

Thanks!

Re: reshape data

saseles — Mon, 11 Jun 2018 22:28:08 GMT

Here is what i tried. But not sure it is right or not. Can you please verify it?

Thanks a lot!

 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 _null_;
if _n_=1 then do;
if 0 then set have;
length o d $50;
  dcl hash H1 (multidata:'y',ordered:'y') ;
   h1.definekey  ('year','o','d') ;
   h1.definedata ('year',"o","d") ;
   h1.definedone () ;
   call missing(o,d);
end;
set have end=last;
array t(*) pc:;
do _n_= 2 to dim(t);
year=vname(t(_n_));
o=t(_n_-1);
d=t(_n_);
rc=h1.add();
end;
if last then h1.output(dataset:'temp2');
run;

data want;
if _n_=1 then do;
if 0 then set temp3;
  dcl hash H1 (dataset:'temp3',multidata:'y') ;
   h1.definekey  ('year','o','d') ;
   h1.definedata ('flows') ;
   h1.definedone () ;
end;
if 0 then set old_1;
array t(*) pc:;
set sort_2(rename=(region=o));
do _n_= 2 to dim(t);
year=vname(t(_n_));
 do n=1 to nobs;
 set sort_2(keep=region rename=(region=d)) nobs=nobs point=n;
flows=0;
rc= h1.find();
 output;
end;
end;
keep year o d flows;
run;

Re: reshape data

novinosrin — Mon, 11 Jun 2018 22:36:06 GMT

@saseles Oh no worries, I will work on the code for you shortly. But did you look at the other accepted solution that doesn't use hashes, which I believe is easier to follow step by step. If not, try to read through it.

And mine does require some cleaning anyways. lol

Re: reshape data

saseles — Mon, 11 Jun 2018 22:36:44 GMT

I tried that one as well. It seems that my problem is easier than that one since I don't need to merge those two data sets. I just got problem to modify the code

Re: reshape data

novinosrin — Mon, 11 Jun 2018 22:57:06 GMT

@saseles Here you go. Have fun

Edit: Adding one more step to get the order right

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 _n_=1 then do;
 dcl hash H1 (dataset:'temp',multidata:'y') ;
   h1.definekey  ('year','from','to') ;
   h1.definedone () ;
end;
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 do;
if h1.check()=0 then number=1;
else number=0;
output;
end;
end;
end;
keep from year to number;
run;

proc sort data=want out=final_want;
by year;
run;

Re: reshape data

saseles — Mon, 11 Jun 2018 23:13:49 GMT

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!

Re: reshape data

novinosrin — Mon, 11 Jun 2018 23:17:36 GMT

Do you mean to reference

"dcl hash H1 (dataset:'temp',multidata:'y') ;

"dcl hash H1 (dataset:'mydata.temp',multidata:'y') ;

If everything points to perm library just use the two level ref name like you did for others

Re: reshape data

saseles — Mon, 11 Jun 2018 23:23:09 GMT

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.

Re: reshape data

novinosrin — Mon, 11 Jun 2018 23:25:01 GMT

Oh gosh, memory failure occurred" "Insufficient memory to execute data step ...."

hash doesn't do well if you have Insufficient memory mate.

I may have to change the code for you. Can you confirm this before I modify again?

Re: reshape data

saseles — Mon, 11 Jun 2018 23:29:41 GMT

I tried again. Same thing

Re: reshape data

novinosrin — Mon, 11 Jun 2018 23:31:24 GMT

Ok no worries. Brb in 10 mins

Re: reshape data

novinosrin — Mon, 11 Jun 2018 23:49:22 GMT

Try this:

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 sql;
create table final_want as
select *, 1 as Number from
 (select year,from,to from want
   intersect
   select year,from,to  from temp)
     union
select *,0 as Number
  from (select year,from,to  from want
         except
      select year,from,to  from temp)
order by year;
quit;

Re: reshape data

saseles — Mon, 11 Jun 2018 23:59:44 GMT

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

Re: reshape data

novinosrin — Tue, 12 Jun 2018 00:02:04 GMT

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;

Re: reshape data

saseles — Tue, 12 Jun 2018 00:16:52 GMT

Trying now!

Re: reshape data

saseles — Tue, 12 Jun 2018 00:30:49 GMT

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.

Re: reshape data

saseles — Tue, 12 Jun 2018 00:34:25 GMT

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!

Re: reshape data

novinosrin — Tue, 12 Jun 2018 00:40:32 GMT

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;