BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jlaw
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
NagendraKumarK
Calcite | Level 5


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

5 REPLIES 5
PGStats
Opal | Level 21

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
jlaw
Calcite | Level 5

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
Opal | Level 21

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
NagendraKumarK
Calcite | Level 5


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
Calcite | Level 5

Thank you, this worked perfectly!

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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