topic Re: Please please help me with intersect and except usage in SAS Procedures

Please please help me with intersect and except usage

ssas — Tue, 09 Mar 2010 14:10:39 GMT

HI All,
I am trying to pull the common rows/observations from the two tables using only 2 column names that are in common.
let us say Table A have 15 columns
Table B have 4 columns
i would like to create a table C with common rows/obeservations that are in table A and Table B.
output table should have common observations with 15 coulmn names

here is my code :
proc sql;
create table chr1 as select a.*,b.*
from mpark1 as a intersect corr all
select janjoin as b
on a.surnames=b.surnames and a.pcode=b.pcode;
quit;

SAS LOG error is as follows:

proc sql;
501 create table chr1 as select a.*,b.*
502 from mpark1 as a intersect corr all
503 janjoin as b
-------
79
504 on a.surnames=b.surnames;
--
22
76
ERROR 79-322: Expecting a SELECT.

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS,
FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

ERROR 76-322: Syntax error, statement will be ignored.

505 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Message was edited by: ssas Message was edited by: ssas

Re: Please please help me with intersect and except usage

RickM — Tue, 09 Mar 2010 14:27:12 GMT

Intersect and except are for common rows/observations, not variables/columns.

Re: Please please help me with intersect and except usage

ssas — Tue, 09 Mar 2010 15:00:38 GMT

Hi ricky,
thanks for reply. I mean to create table with the common observations by using 2 common coloumn names

Re: Please please help me with intersect and except usage

sbb — Tue, 09 Mar 2010 15:14:39 GMT

You may find the SAS COMPARE procedure useful, at least with some part of your interest here. The PROC can generate output and has control option/parameters - suggest at least reviewing the DOC.

Scott Barry
SBBWorks, Inc.

Re: Please please help me with intersect and except usage

RickM — Tue, 09 Mar 2010 15:29:15 GMT

I really only know the very basics of SQL and it sounds like you might need to develop some complex subqueries (though it may be simple and I just don't know SQL well enough to see it)

I would listen to ssb's suggestion, he seems to know his sas.

Re: Please please help me with intersect and except usage

Flip — Tue, 09 Mar 2010 15:30:47 GMT

Try:

proc sql;
create table chr1 as select a.*,b.*
from mpark1 as a INNER JOIN janjoin as b
on a.surnames=b.surnames and a.pcode=b.pcode;
quit; Of course you are going to have some ambiguous columns so you may have to spell out all the columns you want to keep.

Message was edited by: Flip

Re: Please please help me with intersect and except usage

ssas — Wed, 10 Mar 2010 09:34:08 GMT

Thanks a lot Flip for your help.It's really helpful but the thing is when i am using Innerjoin its giving the both records (36k) of Table A(18k records) and Table B(18k records)
I can perform Proc sort with nodup option but ??
is there any alternative for Except like innerjoin.
Thanks in advance.

here is the log;
358 proc sql;
359 create table test10 as select
359! a.member_no,a.fee,a.surnames,a.title,a.pcode,a.expiry,a.joined,a.name,a.dob,a.email,a.cn
359! tr,a.postc,
360 b.member_no,b.fee,b.surnames,b.title,b.pcode,b.expiry,b.joined,b.name,b.dob,b.email,b.cn
360! tr,b.postc
361 from lsfeb10.febld as a INNER JOIN lsmar10.marld as b
362 on a.surnames=b.surnames and a.pcode=b.pcode;
WARNING: Variable Member_No already exists on file WORK.TEST10.
WARNING: Variable FEE already exists on file WORK.TEST10.
WARNING: Variable Surnames already exists on file WORK.TEST10.
WARNING: Variable Title already exists on file WORK.TEST10.
WARNING: Variable PCODE already exists on file WORK.TEST10.
WARNING: Variable Expiry already exists on file WORK.TEST10.
WARNING: Variable Joined already exists on file WORK.TEST10.
WARNING: Variable Name already exists on file WORK.TEST10.
WARNING: Variable Dob already exists on file WORK.TEST10.
WARNING: Variable Email already exists on file WORK.TEST10.
WARNING: Variable cntr already exists on file WORK.TEST10.
WARNING: Variable postc already exists on file WORK.TEST10.
NOTE: Table WORK.TEST10 created, with 37858 rows and 12 columns.

363 quit;
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be
shifted by the "BEST" format.
NOTE: PROCEDURE SQL used (Total process time):
real time 1.90 seconds
cpu time 0.14 seconds

Message was edited by: ssas Message was edited by: ssas

Re: Please please help me with intersect and except usage

Flip — Wed, 10 Mar 2010 13:50:06 GMT

359! a.member_no,a.fee,a.surnames,a.title,a.pcode,a.expiry,a.joined,a.name,a.dob,a.email,a.cn
359! tr,a.postc,
360 b.member_no,b.fee,b.surnames,b.title,b.pcode,b.expiry,b.joined,b.name,b.dob,b.email,b.cn

Which member_no do you want? Pick one or rename one like b.member_no as new_member. for each variable.

Try

select * from d1
intersect corr all
select * from d2;