BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dsrountree
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

4 REPLIES 4
art297
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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