@novinosrin :
Your code is memory-bound only because you make no assumption that the input file is sorted - which is an advantage if memory is plentiful. Note that the TRANSPOSE solution by @Kurt_Bremser does rely on the sorted order. If your hash code took advantage of it as well, the hash table memory footprint would be dictated only by the size of the largest group of records by COUNTY. For example:
data have ;
input county age data2015 data2016 data2017 ;
cards ;
1 1 89 17 79
1 2 6 76 49
1 3 87 5 25
1 4 45 17 1
1 5 78 59 45
2 1 35 4 20
2 2 51 95 46
2 3 76 24 8
2 4 60 66 30
2 5 54 28 41
;
run ;
data want (keep = county age year data) ;
if _n_ = 1 then do ;
dcl hash h (multidata:"Y", ordered:"A") ;
h.definekey ("year") ;
h.definedata ("year", "age", "data") ;
h.definedone () ;
dcl hiter hi ("h") ;
end ;
do until (last.county) ;
set have ;
by county ;
array dd data: ;
do over dd ;
year = input (compress (vname (dd),,"kd"), 4.) ;
data = dd ;
h.add() ;
end ;
end ;
do while (hi.next() = 0) ;
output ;
end ;
h.clear() ;
run ;
It's instructive to see how the task could be approached in the same vein before the advent of the hash object, i.e. using arrays. Obviously, in this case, the array serving in lieu of the hash table should be either sized using a preliminary pass through the data or just made "large enough" (which is what's done below):
data want (keep = county age year data) ;
do j = 1 by 1 until (last.county) ;
set have ;
by county ;
array dd (i) data: ;
array tt [0:999, 9999] _temporary_ ;
do over dd ;
tt [0,j] = age ;
tt [i,j] = dd ;
end ;
end ;
do over dd ;
year = input (compress (vname (dd),,"kd"), 4.) ;
do k = 1 to j ;
age = tt [0,k] ;
data = tt [i,k] ;
output ;
end ;
end ;
run ;
With a more dynamic approach, i.e. with pre-sizing the array bounds using a preliminary pass through HAVE, it could look thusly:
/* size up array TT */
data _null_ ;
do j = 1 by 1 until (last.county) ;
set have end = z ;
by county ;
end ;
retain jmax ;
jmax = jmax max j ;
array dd data: ;
if z ;
call symputx ("i", dim (dd)) ;
call symputx ("j", jmax ) ;
run ;
/* execute */
data want (keep = county age year data) ;
do j = 1 by 1 until (last.county) ;
set have ;
by county ;
array dd (i) data: ;
array tt [0:&i, &j] _temporary_ ;
do over dd ;
tt [0,j] = age ;
tt [i,j] = dd ;
end ;
end ;
do over dd ;
year = input (compress (vname (dd),,"kd"), 4.) ;
do k = 1 to j ;
age = tt [0,k] ;
data = tt [i,k] ;
output ;
end ;
end ;
run ;
Kind regards
Paul D.
