topic Re: re: Selecting Rows in SAS Procedures

re: Selecting Rows

twildone — Tue, 07 Oct 2014 18:34:23 GMT

Hi.....I have two variables - Client & Code. I would like to extract a list of Clients with their code where the code is either a 1 or 2 or 3 as well as the other codes for only these same clients. Can this done using a Proc SQL?...Thanks in Advance.

Re: re: Selecting Rows

PGStats — Tue, 07 Oct 2014 18:44:25 GMT

Yes, use an EXISTS clause:

proc sql;

create table want as

select *

from myTable as A

where exists (select * from myTable where client=A.client and code in (1, 2, 3));

quit;

(untested)

Re: re: Selecting Rows

stat_sas — Tue, 07 Oct 2014 18:46:37 GMT

data have;

input client code;

datalines;

1 1

1 2

2 1

2 2

2 3

3 1

3 2

3 4

3 5

4 8

4 1

;

data list;

input client;

datalines;

;

proc sql;

select * from have

where client in (select client from list);

quit;

Re: re: Selecting Rows

twildone — Tue, 07 Oct 2014 23:03:53 GMT

Thanks PG.....if I had 5 variables - Client, date_processed, code, eff_date, and ex_date and wanted to select the row for the Client if the eff_dates and ex_dates are the same for the same client based on the maximum of date_processed to eliminate duplicates. Would a similar proc sql achieve this? Thanks in Advance.

Re: re: Selecting Rows

PGStats — Wed, 08 Oct 2014 01:37:04 GMT

Seems quite different. Please post as a new question, with a small example. - PG