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


Hi...I am trying to select a certain group of clients from a dataset who have a code of either 61, 62, 77, 78. However, these same clients may also have other codes assign to them as well. So I am trying to capture the complete list of clients but they must have at least a code of 61, 62, 77, 78. I am running the following code but it seems running forever so I am assuming I must be missing something...hopefully someone can see what is missing or might need to be done to achieve the same results......Thanks in Advance.

PROC SQL NOPRINT;

     CREATE TABLE Need AS

           SELECT *

           FROM work.dpa AS A

           WHERE EXISTS

                (SELECT *

                FROM work.dpa

                WHERE ((dpa.client=A.client)

                     AND (dpa.code in (61, 62, 77, 78))

                     AND (dpa.status = 1)));

QUIT;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Is there some reason you want to match every record in the data set with itself?

It would help to provide a small amount of data and what you want for a result as you are doing more than selecting records based on codes.

If you want to select all of the clients with the code and status specified and then retrieve all records for those clients:

proc sql;

     create table need as

     select b.*

     from (select * from work.dpa where code in (61,62,77,78) and status=1) as a

     left join work.dpa as b

     on a.client=b.client;

quit;

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20

Correlated sub-queries could cost.

Another option could be building a client list in the sub-query instead, and filter using IN(sub-query).

Data never sleeps
Loko
Barite | Level 11

Hello,

Maybe this is what you are looking from:

PROC SQL NOPRINT;

     CREATE TABLE Need AS

           SELECT *

           FROM sashelp.class AS A

           WHERE name in

                (SELECT name

                FROM sashelp.class

                WHERE

                     age in (14, 15, 16)

                    AND (sex = 'M'));

QUIT;

ballardw
Super User

Is there some reason you want to match every record in the data set with itself?

It would help to provide a small amount of data and what you want for a result as you are doing more than selecting records based on codes.

If you want to select all of the clients with the code and status specified and then retrieve all records for those clients:

proc sql;

     create table need as

     select b.*

     from (select * from work.dpa where code in (61,62,77,78) and status=1) as a

     left join work.dpa as b

     on a.client=b.client;

quit;

twildone
Pyrite | Level 9


Hi Ballardw,

I don't want to match every record with itself. I want to extract a list of clients from the dataset labelled dpa who have either a code  of 61, 62, 77, or 78. and from this list go back to the dataset labelled dpa and extract all records for these client. The end result is to obtain a complete set of records for these clients irrrespective of their code.....hope this helps.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I would agree with Ballardw there.  Do it it two selects and join the results.  A subquery is great, but can get slower with larger data so:

proc sql;

     select     *

     from       DPA DPA

     right join (select CLIENT from DPA where CODE in (61,62,77,78)) C

     on           DP.CLIENT=C.CLIENT;

run;

In the above I right join the big table onto the list of clients having that code.

stat_sas
Ammonite | Level 13

PROC SQL NOPRINT;

     CREATE TABLE Need AS

           SELECT *

           FROM work.dpa

                WHERE CLIENT in (select client from work.dpa

                     where code in (61, 62, 77, 78)

                     AND status = 1);

QUIT;

BartDekeyser
Calcite | Level 5

Why so much selects in select...

proc sql;

create table need as

select * from work.dpa  where code in (61,62,77,78) and status = 1;

quit;

%global cnt_clients;

%macro CalcObs;

proc sql noprint;

select count(client) into: cnt_clients

from need;

quit;

%mend CalcObs;

Like that you will know that there are observations selected (I suppose that's why you want to use the EXISTS.)

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Principally because the code provided is one step, yours is two.  Also there is no need for macros/variables or the macro pre-processor to do anything.  Also the integrated sub-query would be optimized by the SQL engine, whereas the two stage approach would only be optimized within itself.  He also stated he wanted the records from the dataset which had those client numbers.  Hope this helps explain why we went down that path.

Ksharp
Super User

You'd better post some dummy data and the output you need.

data have;
input client $ code;
cards;
x 62
x 32
y 61
y 77
y 21
z 23 
z 43
;
run;
proc sql;
create table want as
 select * from have group by client having sum(code=61) gt 0 or sum(code=62) gt 0 or sum(code=77) gt 0 or sum(code=78) gt 0 ;
quit;

Xia Keshan

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
  • 9 replies
  • 774 views
  • 6 likes
  • 8 in conversation