Help using Base SAS procedures

Please please help me with intersect and except usage

Reply
Contributor
Posts: 43

Please please help me with intersect and except usage

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
Regular Contributor
Posts: 165

Re: Please please help me with intersect and except usage

Intersect and except are for common rows/observations, not variables/columns.
Contributor
Posts: 43

Re: Please please help me with intersect and except usage

Hi ricky,
thanks for reply. I mean to create table with the common observations by using 2 common coloumn names
Super Contributor
Super Contributor
Posts: 3,174

Re: Please please help me with intersect and except usage

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.
Regular Contributor
Posts: 165

Re: Please please help me with intersect and except usage

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.
Super Contributor
Posts: 359

Re: Please please help me with intersect and except usage

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

Re: Please please help me with intersect and except usage

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
Super Contributor
Posts: 359

Re: Please please help me with intersect and except usage

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