topic how do I merge two tables with same columns if I just want data that is in both tables based on cert in SAS Data Science

how do I merge two tables with same columns if I just want data that is in both tables based on cert

wheddingsjr — Mon, 14 Nov 2016 16:04:38 GMT

how do I merge two tables with same columns if I just want data that is in both tables based on certain columns? I tried using this but it doesnt seem to work.

PROC SQL;

CREATE Table parta as

Select *

from step2

where SERVCODN IN ('96372');

quit;

PROC SQL;

CREATE Table partb as

Select *

from step2

where SERVCODN NOT IN ('96372');

PROC SQL;

Create table results as

SELECT parta.*,partb.*

FROM parta, partb

WHERE parta.memno = partb.memno

and parta.claimno = partb.claimno

and parta.APPL_FULL_SVC_NBR = partb.APPL_FULL_SVC_NBR;

quit;

Re: how do I merge two tables with same columns if I just want data that is in both tables based on

anoopmohandas7 — Tue, 15 Nov 2016 00:56:51 GMT

I ran your code and it's working i.e giving rows that have common values between datasets. I strongly recommnend you to check your data to see if you have a match. Also let me know if you are expecting something else. Good Luck.

data step2 ;
input SERVCODN $ memno claimno APPL_FULL_SVC_NBR;
datalines ;
96372 10 96 100
96372 27 50 102
96520 27 50 102
90222 10 96 100
90001 22 85 104
;
run ;

PROC SQL;
CREATE Table parta as
Select *
from step2
where SERVCODN IN ('96372');
quit;

PROC SQL;
CREATE Table partb as
Select *
from step2
where SERVCODN NOT IN ('96372');

proc print data=parta;run ;
proc print data=partb;run ;

PROC SQL;

SELECT parta.*,partb.*
FROM parta, partb
WHERE parta.memno = partb.memno
and parta.claimno = partb.claimno
and parta.APPL_FULL_SVC_NBR = partb.APPL_FULL_SVC_NBR;
quit;

Re: how do I merge two tables with same columns if I just want data that is in both tables based on

ballardw — Wed, 16 Nov 2016 22:01:54 GMT

Please describe in a bit of detail how it "doesn't work". We do not have your data so cannot duplicate your results. It appears that the code could work but: If you get error messages then post the log with the error messages. It is best to post the log information in an entry box opened with the "run" icon as some of the info in the error message relies on position that the main forum entry will remove.

If the result is unexpected or no output then provide examples of all input data and the desired output. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will help you post SAS data set(s) as data step code to allow us to recreate your actual data. If you post text or picture we have to write code and guess as to your variable types and may get something that works for that but not your data.

Your code could fail because 1) there are no records with SERCOCDN = '96372' or 2) none of the records that do not have SERVCODN = '96372' have a match on all three of Memno, Claimno and APPL_FULL_SVC_NBR.

If you wanted to see if any of the three match then you want to use OR instead of AND in the where clause.