Help using Base SAS procedures

common observations

Reply
Contributor
Posts: 43

common observations

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
Super User
Posts: 5,256

Re: common observations

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
Data never sleeps
Respected Advisor
Posts: 3,890

Re: common observations

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;
Contributor
Posts: 43

Re: common observations

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
Respected Advisor
Posts: 3,890

Re: common observations

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
Ask a Question
Discussion stats
  • 4 replies
  • 120 views
  • 0 likes
  • 3 in conversation