topic Re: PROC SQL SORT Vs. PROC SORT in SAS Procedures

PROC SQL SORT Vs. PROC SORT

Dsrountree — Fri, 27 Dec 2013 18:50:19 GMT

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;

Re: PROC SQL SORT Vs. PROC SORT

art297 — Fri, 27 Dec 2013 19:22:18 GMT

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;

Re: PROC SQL SORT Vs. PROC SORT

DBailey — Fri, 27 Dec 2013 20:41:12 GMT

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;

Re: PROC SQL SORT Vs. PROC SORT

Dsrountree — Fri, 27 Dec 2013 22:23:47 GMT

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

Re: PROC SQL SORT Vs. PROC SORT

DBailey — Mon, 30 Dec 2013 14:11:29 GMT

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