Hi

I just tried out this comparison of three methods for a relatively large dataset - about 5 Million numeric observations :

*CREATE TEST DATA*;
        DATA TEST(KEEP=I);
        DO I=1 TO 1000000;
        DO J=1 TO 10;
        IF RANUNI(0)>0.5 THEN OUTPUT;
        END;
        END;
        RUN;

*METHOD 1 : PROC SORT*;
        PROC SORT DATA=TEST OUT=TEST2 NODUPLICATES; BY I; RUN;

*METHOD 2 : PROC SQL*;
        PROC SQL;
        CREATE TABLE TEST3 AS
        SELECT DISTINCT I
        FROM TEST;
        QUIT;

*METHOD 3 : DATA STEP ARRAY*;
        DATA TEST4(KEEP=I);
        ARRAY TEMP{1000000} _TEMPORARY_;
        IF _N_=1 THEN DO;
                DO J=1 TO MAXJ;
                SET TEST NOBS=MAXJ POINT=J;
                TEMP{I}=SUM(TEMP{I},1);
                END;
        END;
        DO I=1 TO 1000000;
        IF TEMP{I}>0 THEN OUTPUT;
        END;
        STOP;
        RUN;

*FINALLY : CHECK RESULTS ARE ALL SAME*;
        DATA LAST;
        SET TEST2(RENAME=(I=I2));
        SET TEST3(RENAME=(I=I3));
        SET TEST4(RENAME=(I=I4));
        IF I2 NE I3;
        IF I2 NE I4;
        RUN;

From the log, the timings were as follows :

*METHOD 1 : PROC SORT*;
      real time           1:29.50
      cpu time            24.65 seconds

*METHOD 2 : PROC SQL*;
      real time           51.12 seconds
      cpu time            28.26 seconds

*METHOD 3 : DATA STEP ARRAY*;
      real time           36.26 seconds
      cpu time            6.57 seconds

So, method 3 was quickest for a large dataset - it's slowest for small datasets.

I ran this a handful of times - results fluctuated because usage of our server fluctuated - but got similar time savings with method 3.

Neale Parsons
Business Analyst
e-mail : Parsonsn@BUPA.com
Tel : 44 (0) 1784 891 265
Fax : 44 (0) 1784 891 281
BUPA, Commercial Analysis Team, Thameside House,
South Street, Staines, Middx, TW18 4TL