topic Re: Rewrite proc sql to more effeceint query? in SAS Programming

Rewrite proc sql to more effeceint query?

jlaw — Tue, 15 Jan 2013 00:44:36 GMT

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;

Re: Rewrite proc sql to more effeceint query?

PGStats — Tue, 15 Jan 2013 01:08:34 GMT

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)?

Re: Rewrite proc sql to more effeceint query?

NagendraKumarK — Tue, 15 Jan 2013 16:44:29 GMT

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;

Re: Rewrite proc sql to more effeceint query?

jlaw — Wed, 16 Jan 2013 18:23:17 GMT

Thank you, this worked perfectly!

Not sure why this worked so much faster but it did

Re: Rewrite proc sql to more effeceint query?

jlaw — Wed, 16 Jan 2013 18:27:52 GMT

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

Re: Rewrite proc sql to more effeceint query?

PGStats — Wed, 16 Jan 2013 20:28:47 GMT

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.