Help using Base SAS procedures

PROC SQL SORT Vs. PROC SORT

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

PROC SQL SORT Vs. PROC SORT

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;


Accepted Solutions
Solution
‎12-27-2013 02:22 PM
PROC Star
Posts: 7,364

Re: PROC SQL SORT Vs. PROC SORT

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;

View solution in original post


All Replies
Solution
‎12-27-2013 02:22 PM
PROC Star
Posts: 7,364

Re: PROC SQL SORT Vs. PROC SORT

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;

Super Contributor
Posts: 578

Re: PROC SQL SORT Vs. PROC SORT

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;




Contributor
Posts: 52

Re: PROC SQL SORT Vs. PROC SORT

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

Super Contributor
Posts: 578

Re: PROC SQL SORT Vs. PROC SORT

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 607 views
  • 4 likes
  • 3 in conversation