PROC SQL SORT Vs. PROC SORT

[Dsrountree]

How do I get the PROC SQL below to produce the same results accomplished using PROC SORT and the DATA SET BY IF RUN?

DATA TESTSORT;

     INPUT CLAIMNUMBER $ SEQNUM $ BEGSERVICEDATE ddmmyy10. PROVNUM $ PROVLOC $ FFSORCAPIND $ CLAIMSTATUS $ PAYMENTSTATUS $;

     FORMAT BEGSERVICEDATE ddmmyy10.;

     DATALINES;

123456 001 01/01/2013 00007 999 C 11 31                                                                                                               

123456 002 01/01/2013 00007 999 C 11 11                                                                                                               

121212 001 02/01/2013 00006 998 C 11 11                                                                                                              

555555 001 01/01/2013 00008 997 C 11 11                                                                                                              

555555 002 01/01/2013 00008 997 C 11 31                                                                                                              

555555 003 01/01/2013 00008 998 C 11 31                                                                                                              

;

RUN; 

PROC SORT DATA=  TESTSORT;                                                                                                              

BY CLAIMNUMBER DESCENDING SEQNUM;                                                                                                            

RUN;

DATA COUNTTESTSORT;                                                                                                                          

SET TESTSORT;                                                                                                                           

BY CLAIMNUMBER;                                                                                                                           

IF FIRST.CLAIMNUMBER;                                                                                                                     

RUN;  

PROC SQL;

     CREATE TABLE TEST AS

           SELECT

                CLAIMNUMBER,

                SUBSTR(CLAIMNUMBER,1,13) AS SUBCLM,

                MAX(SEQNUM)AS SEQNUM,

                BEGSERVICEDATE,

                PROVNUM,

                PROVLOC,

                FFSORCAPIND,

                CLAIMSTATUS,

                PAYMENTSTATUS

           FROM TESTSORT

                GROUP BY SUBCLM, SEQNUM

                     ORDER BY SUBCLM, SEQNUM;

QUIT;

[art297 · Accepted solution]

Derrick,

Your proc sort and data step code are doing two things: (1) sorting the original file and then (2) creating a 2nd file that only has one record for each CLAIMNUMBER.

If SEQNUM values are unique within each CLAIMNUMBER, then you could use something like the following:

PROC SQL;

  CREATE TABLE TESTSORT AS

    SELECT *

      FROM TESTSORT

        ORDER BY CLAIMNUMBER, SEQNUM DESCENDING

  ;

  CREATE TABLE COUNTTESTSORT AS

    SELECT *

      FROM TESTSORT

        group BY CLAIMNUMBER

          having SEQNUM eq max(SEQNUM)

  ;

QUIT;

[DBailey]

This is from your counttestsort:

121212    001    02/01/2013    00006    998    C    11    11

123456    002    01/01/2013    00007    999    C    11    11

555555    003    01/01/2013    00008    998    C    11    31

This is from test2 created below:

121212    001    02/01/2013    00006    998    C    11    11

123456    002    01/01/2013    00007    999    C    11    11

555555    003    01/01/2013    00008    998    C    11    31

PROC SQL;

     CREATE TABLE TEST2 AS

           SELECT

                CLAIMNUMBER,

                SEQNUM,

                BEGSERVICEDATE,

                PROVNUM,

                PROVLOC,

                FFSORCAPIND,

                CLAIMSTATUS,

                PAYMENTSTATUS

           FROM TESTSORT t

     where

          seqnum=(select max(seqnum) from testsort where claimnumber=t.claimnumber)

                     ORDER BY CLAIMNUMBER, SEQNUM;

QUIT;

[Dsrountree]

I'm trying to mark both answers as correct, but that isn't an option in this community.

Thank you so much...

healthcare data is a nightmare to parse...

Especially since Im new to SAS

[DBailey]

Not a problem....Arthur's answer is better anyway...