topic How do I see if records with matching keys are in both files in New SAS User

How do I see if records with matching keys are in both files

fpascal — Sun, 28 Oct 2018 01:41:51 GMT

How do I see if records with matching keys are in both files and then output only the records that are in the second file into a new file?

proc sql;
create table cohort2 as
select *
from etl.inp_claims_lds2013_2014_lejr etl.inp_claims_lds2015_2016_lejr
where etl.inp_claims_lds2015_2016_lejr.desy_sort_key in (select
desy_sort_key from etl.inp_claims_lds2013_2014_lejr);
quit;

Re: How do I see if records with matching keys are in both files

novinosrin — Sun, 28 Oct 2018 01:46:53 GMT

What do you think is wrong with your code. Seems ok or am i not understanding your description?

Re: How do I see if records with matching keys are in both files

fpascal — Sun, 28 Oct 2018 01:50:15 GMT

75
76 * Log/List Options *;
77 options ls=120 ps=42 missing=' ' nocenter validvarname=upcase compress=binary;
78 * Output SAS Library *;
79 libname etl 'D:\Medicare\05_etl_output\';
NOTE: Libref ETL was successfully assigned as follows:
Engine: V9
Physical Name: D:\Medicare\05_etl_output
80
81
82 proc sql;
83 create table cohort2 as
84 select *
85 from etl.inp_claims_lds2013_2014_lejr etl.inp_claims_lds2015_2016_lejr
_
22
200
ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING,
INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 200-322: The symbol is not recognized and will be ignored.

86 where etl.inp_claims_lds2015_2016_lejr.desy_sort_key in (select
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE,
LT, LTT, NE, NET, NOT, NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.

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

87 desy_sort_key from etl.inp_claims_lds2013_2014_lejr);
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
88 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

Re: How do I see if records with matching keys are in both files

novinosrin — Sun, 28 Oct 2018 02:02:22 GMT

my tired eyes didn't capture the missing comma in from clause

from etl.inp_claims_lds2013_2014_lejr , etl.inp_claims_lds2015_2016_lejr

check that.

also, at late evening here don't trust my eyes

Re: How do I see if records with matching keys are in both files

fpascal — Sun, 28 Oct 2018 02:05:15 GMT

Almost there

80
81
82 proc sql;
83 create table cohort2 as
84 select *
85 from etl.inp_claims_lds2013_2014_lejr, etl.inp_claims_lds2015_2016_lejr
86 where etl.inp_claims_lds2015_2016_lejr.desy_sort_key in (select
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE,
LT, LTT, NE, NET, NOT, NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.

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

87 desy_sort_key from etl.inp_claims_lds2013_2014_lejr);
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
88 quit;

Re: How do I see if records with matching keys are in both files

novinosrin — Sun, 28 Oct 2018 02:06:47 GMT

what's your objective

Are you after this logic?

proc sql;
select *
from b
where key in (select key from a);
quit;

Re: How do I see if records with matching keys are in both files

fpascal — Sun, 28 Oct 2018 02:11:15 GMT

I want to take check each record in file a to see if it is in file b. If so I want to add it to a new file c.

Re: How do I see if records with matching keys are in both files

novinosrin — Sun, 28 Oct 2018 02:45:28 GMT

You are better off posting a sample of your data and the requirement

Re: How do I see if records with matching keys are in both files

PGStats — Sun, 28 Oct 2018 03:07:14 GMT

SAS/SQL doesn't support 3-level variable names. You must use aliasses :

from etl.inp_claims_lds2013_2014_lejr as a, etl.inp_claims_lds2015_2016_lejr as b
       where b.desy_sort_key in (select
        desy_sort_key from etl.inp_claims_lds2013_2014_lejr)

Re: How do I see if records with matching keys are in both files

fpascal — Sun, 28 Oct 2018 04:17:12 GMT

thanks - below it states it cannot find my files but they are in library ETL. Is there something else i need to add

73 libname etl 'D:\Medicare\05_etl_output\';
NOTE: Libref ETL was successfully assigned as follows:
Engine: V9
Physical Name: D:\Medicare\05_etl_output
74
75 proc sql;
76 create table cohort as
77 select *
78 from etl.inp_claims_2013_2014_lejr as a, etl.inp_claims_2015_2016_lejr as b
79 where b.desy_sort_key in (select
80 desy_sort_key from a);
ERROR: File ETL.INP_CLAIMS_2013_2014_LEJR.DATA does not exist.
ERROR: File ETL.INP_CLAIMS_2015_2016_LEJR.DATA does not exist.
ERROR: File WORK.A.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
81 quit;