topic Re: Making subsets of a table. in SAS Programming

Making subsets of a table.

deleted_user — Fri, 30 Oct 2009 08:42:13 GMT

I have a table A, containing individuals with variables ID(=identity) and seven other variables, VAR1-VAR7.

VAR1 can have the values 0, 1 or 2.

From table A, I want to make two other tables, B and C.

In B I want all records where VAR1=0. That is easy to get.

In C I want all records for the individuals not found in B.

Concerning number of records this means that B+C le A, because the individuals found in B could also have records in A, where VAR1=1 or VAR1=2.

How do I get C?

Re: Making subsets of a table.

FredrikE — Fri, 30 Oct 2009 09:51:08 GMT

proc sql;
create table_c as
select *
from table_a
where id not in (select distinct id from table_b)
;
quit;

Re: Making subsets of a table.

deleted_user — Fri, 30 Oct 2009 17:49:32 GMT

data b c;
set a;
if var1=0 then output b;
else output c;
run;

Oops, not. too quick to answer. C could have the other var1=1 and var1=2 records for people in B.

So a second step is reqquired.
could use proc sql with not in as above or "not exist".
Could also use

proc sort data=b; by id;
proc sort data=c; by id;

data c;
merge b(in=inb) c(in=inc);
by id;
if inc and not inb then output;
run;

but I think the sql is the better way to go. Message was edited by: Chuck