topic Re: common observations in SAS Procedures

common observations

ssas — Tue, 09 Dec 2008 21:37:33 GMT

Hi,

I have 20 tables and each table got around 650 observations with 12 variables.
6 variable(column names are same) they are firstname lastname postcode email id.
i would like to pick out the common observations which got same values in the all tables.

I am tried to make all the observations to upppercase and merging them using (in) in merge,
if a and b ............
but i am unable to workout.
its some thing like
if a and/or b and/or c ...
could any one please suggest the correct logic or datastep or proc sql

Thanks,
suresh

Re: common observations

LinusH — Tue, 09 Dec 2008 22:05:35 GMT

If you mean "common observations" as has common values in the BY/id columns, it would be something like in your example:

data common;
merge a(in=a) b(in=b) c(in=c) d(in=d) e(in=e) f(in=f);
by firstname lastname postcode email id;
if a and b and c and d and e and f;
run;

All tables need to be sorted on the BY columns.

/Linus

Re: common observations

Patrick — Wed, 10 Dec 2008 07:51:08 GMT

This is a SQL variant:

proc sql;
create table ObsAndVarsCommon as
select * from Table1
intersect corr all
select * from Table2
intersect corr all
select * from Table3
.....and so on and so on
;
quit;

Re: common observations

ssas — Wed, 10 Dec 2008 09:02:52 GMT

I mean
i have 20 shows ( 1 table for each show).
i would like to find out how many people attended how many shows with respect to their
first name, last name, postcode and gender.

Thanks,
suresh

Re: common observations

Patrick — Thu, 11 Dec 2008 08:03:48 GMT

data Vall / view=Vall;
set a(in=a) b(in=b) c(in=c) d(in=d) e(in=e) f(in=f);
if a then show='a';
else if b then show='b';
else if....
run;

proc tabulate data=Vall;
.....and then work out what list you really want.

HTH
Patrick