Re: Row Selection

Accepted Solution Solved
Reply
Regular Contributor
Posts: 221
Accepted Solution

Re: Row Selection


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;


Accepted Solutions
Solution
‎10-09-2014 10:21 AM
Grand Advisor
Posts: 10,223

Re: Row Selection

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


All Replies
Esteemed Advisor
Posts: 5,198

Re: Row Selection

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
Super Contributor
Posts: 305

Re: Row Selection

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;

Solution
‎10-09-2014 10:21 AM
Grand Advisor
Posts: 10,223

Re: Row Selection

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;

Regular Contributor
Posts: 221

Re: Row Selection


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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Row Selection

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.

Trusted Advisor
Posts: 1,203

Re: Row Selection

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;

Occasional Contributor
Posts: 9

Re: Row Selection

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Row Selection

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.

Grand Advisor
Posts: 9,584

Re: Row Selection

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

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 383 views
  • 6 likes
  • 8 in conversation