topic Re: Row Selection in SAS Programming

Re: Row Selection

twildone — Thu, 09 Oct 2014 14:07:45 GMT

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;

Re: Row Selection

LinusH — Thu, 09 Oct 2014 14:16:59 GMT

Correlated sub-queries could cost.

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

Re: Row Selection

Loko — Thu, 09 Oct 2014 14:19:20 GMT

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;

Re: Row Selection

ballardw — Thu, 09 Oct 2014 14:21:37 GMT

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;

Re: Row Selection

twildone — Thu, 09 Oct 2014 14:45:45 GMT

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.

Re: Row Selection

RW9 — Thu, 09 Oct 2014 14:58:35 GMT

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.

Re: Row Selection

stat_sas — Thu, 09 Oct 2014 15:04:35 GMT

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;

Re: Row Selection

BartDekeyser — Fri, 10 Oct 2014 06:55:13 GMT

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

Re: Row Selection

RW9 — Fri, 10 Oct 2014 07:59:56 GMT

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.

Re: Row Selection

Ksharp — Fri, 10 Oct 2014 12:20:57 GMT

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