11-13-2015 03:11 PM - edited 11-13-2015 03:50 PM
I have a table with 850k obs and 20 variables and I need to do this:
To do this I wrote a program using a macro:
%do n=1 %to 850000;
if _n_=&n. THEN CALL SYMPUT(values for the “n” observation);
if _n_ le 1000;
PROC means DATA=calc2 NOPRINT ;
OUTPUT OUT=t3 N=N MEAN(raroc)=MEANr STD(raroc)=STDr p10(raroc)=p10r p25(raroc)=p25r p50(raroc)=p50r p75(raroc)=p75r p90(raroc)=p90r;
SET p.tacum3 T3;
This works perfectly, but it takes too long, basically because it is reading the 850k table in each iteration, and I have to do 850k iterations.
If the entire calculation would take 1 second for each obs, I would need 10 days to complete the job. And It takes 3 seconds… so I need help.
So the questions is: Is there any way to do this faster? I guess I have to rewrite the code doing this in memory with not physical reading of the big table…but how!
11-13-2015 03:30 PM
Without knowing the calculations, just one part stands out. The first DATA step could be revised:
set table850k (firstobs=&n obs=&n);
call symput(values for this observation);
But that won't be enough savings. You might have to share some of the details of the code.
11-13-2015 04:02 PM - edited 11-13-2015 04:06 PM
This looks like a standard Euclidean distance? Have you checked out proc distance? You can do Euclidean distance with or without standardization.
That being said, if you wanted to write something yourself I would also look at the hash table method suggested above to do this in memory or IML if you have access to that. If you think about these distances being calculated as entries in a 850000 x 850000 matrix, it will be symmetric (distance between 1 and 2 is same as distance between 2 and 1) so you maybe doing many more comparisons than are actually necessary. You should have sufficient distance information in one half of that hypothetical distance matrix.
11-13-2015 11:46 PM
In theory, you can get the 1000 closest neighbors of every observation efficiently with proc modeclus :
ods _all_ close; proc modeclus data=table850k dk=1001 neighbor; var geo1-geo12; id myObsId; ods output Neighbor=myNeighbors; run; ods listing;
...in practice, it might run into memory space problems. I used it succesfully for finding 5 nearest neighbors among 200,000 positions defined by two variables.
11-16-2015 10:33 AM
Given the detail you added, this would be a significant improvement for the final step:
proc append data=T3 base=p.tacum3;
The savings become larger and larger as you iterate through the loop 850K times, so you may not notice as much in the way of savings if you test with a small number of iterations.
There may be significant value in some of the other approaches mentioned by other posters ... I'm more focused on your original code. Good luck.