Solved
Contributor
Posts: 59

# 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: 8,164

## 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;

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

## 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

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: 59

## 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