BookmarkSubscribeRSS Feed
ssas
Calcite | Level 5
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
7 REPLIES 7
RickM
Fluorite | Level 6
Intersect and except are for common rows/observations, not variables/columns.
ssas
Calcite | Level 5
Hi ricky,
thanks for reply. I mean to create table with the common observations by using 2 common coloumn names
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
RickM
Fluorite | Level 6
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.
Flip
Fluorite | Level 6
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
ssas
Calcite | Level 5
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
Flip
Fluorite | Level 6
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1401 views
  • 0 likes
  • 4 in conversation