Rewrite proc sql to more effeceint query?

[jlaw]

lDoes anyone have a more effeceint way to write this query?

PROC SQL;
CREATE TABLE X.J2 AS
Select distinct a.customernum, a.patientid

FROM X.J1 A
where groupend = (select max(groupend) from X.J1 b
where a.patientid = b.patientid and a.customernum=b.customernum);
QUIT;

[PGStats]

The groupend condition in this query changes nothing to the result except possibly exclude cases where groupend is missing. The way its written there, your query is equivalent to

PROC SQL;

create table X.J2 as

select distinct customernum, patientid

from X.J1

where groupend is not missing;

quit;

What is the intended purpose of your query (in plain language)?

PG

[jlaw]

Thank you for response.  What I was attempting to do was keep all distinct customernum, patientid records, but keep the most current record.  GroupEnd corresponds to a date in the following format: 20130115.

so for example if there was record with the same customernum & patientid then I would only want to keep the one that had the most recent groupend date.

Example data:

PATIENTID     CUSTOMERNUM     GROUPEND

123123             ABC987                    20130115

123123             ABC987                    20110115

DESIRED RESULT:

PATIENTID     CUSTOMERNUM     GROUPEND

123123               ABC987                    20130115

[PGStats]

Well, in that case, provided you with the correct answer. It can be written more conventionally as :

PROC SQL;

CREATE TABLE RES AS

SELECT DISTINCT CUSTOMERNUM, PATIENTID, GROUPEND

FROM HELLO

GROUP BY CUSTOMERNUM, PATIENTID

HAVING GROUPEND = MAX(GROUPEND);

QUIT;

You don't need the DISTINCT option if the groupend dates are unique for each value of patientid and customernum.

PG

[NagendraKumarK · Accepted solution]

DATA HELLO;
INPUT  CUSTOMERNUM PATIENTID GROUPEND;
DATALINES;
1234 1001 1045
1234 1001 1046
1234 1001 1047
1235 1002 1048
1235 1002 1049
1235 1003 1134
1235 1003 1137
1235 1003 1138
1236 1003 1234
1236 1002 1345
;
RUN;

PROC SQL;
CREATE TABLE RES AS
(SELECT DISTINCT CUSTOMERNUM,PATIENTID,GROUPEND FROM HELLO
GROUP BY CUSTOMERNUM,PATIENTID HAVING GROUPEND=MAX(GROUPEND)
);
RUN;

[jlaw]

Thank you, this worked perfectly!

Not sure why this worked so much faster but it did