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;
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;
Correlated sub-queries could cost.
Another option could be building a client list in the sub-query instead, and filter using IN(sub-query).
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;
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;
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.
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.
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;
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.)
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.