Rewrite proc sql to more effeceint query?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Rewrite proc sql to more effeceint query?

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;


Accepted Solutions
Solution
‎01-15-2013 11:44 AM
Occasional Contributor
Posts: 14

Re: Rewrite proc sql to more effeceint query?


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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,663

Re: Rewrite proc sql to more effeceint query?

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

PG
Occasional Contributor
Posts: 10

Re: Rewrite proc sql to more effeceint query?

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

Respected Advisor
Posts: 4,663

Re: Rewrite proc sql to more effeceint query?

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

PG
Solution
‎01-15-2013 11:44 AM
Occasional Contributor
Posts: 14

Re: Rewrite proc sql to more effeceint query?


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;

Occasional Contributor
Posts: 10

Re: Rewrite proc sql to more effeceint query?

Thank you, this worked perfectly!

Not sure why this worked so much faster but it did Smiley Happy

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 251 views
  • 4 likes
  • 3 in conversation