topic Re: INLIST in SAS Enterprise Guide

INLIST

bhca60 — Tue, 24 May 2022 04:03:39 GMT

proc sql;
create table Btable
as select
    id,
    name,
    dob
FROM table1
UNION
SELECT 
    id,
	name,
	dob
FROM table2
where id IN ('12345' '123456');
quit;

I have tried everything to filter on these two ID's but i keep getting errors. I tried separating with a comma, semicolon - nothing seems to work.

Re: INLIST

SASKiwi — Tue, 24 May 2022 04:26:29 GMT

There is no obvious syntax error in your posted code. You need to post your complete SAS log including notes and errors so we can see what is happening.

Re: INLIST

Kurt_Bremser — Tue, 24 May 2022 04:36:54 GMT

Please post the complete log of your SQL step by cppy/pasting it into a window opened with this button:

Re: INLIST

ballardw — Tue, 24 May 2022 14:23:46 GMT

AS an absolute minimum include the actual text of any errors. Much better is to show the entire log for the procedure run with the code and all notes, warnings or errors associated with that procedure. Copy the text from the LOG, on the forum open a text box using the </> icon above the message window and paste the copied text.

Is it as simple as your ID variable is numeric and you are getting errors about the wrong type such as

ERROR: Expression using IN has components that are of different data types.

Then don't put quotes around the values.

Re: INLIST

bhca60 — Tue, 24 May 2022 16:25:43 GMT

I've gotten that error before but I got this error for the above:
ERROR: Expression using IN has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL WHERE clause optimization.

Nothing seems to work - single quotes, double quotes, commas, no commas, semicolons...sometimes it just ignores the filter entirely and brings in all data. when i remove the quotes, it brings in all data.

Re: INLIST

Tom — Tue, 24 May 2022 16:41:45 GMT

Is ID a numeric variable or a character variable? Does it have a format attached to it? Which format? Is it a SAS system format or a user defined format. Remember you need to test for the actual values, not the values are displayed by the format.

Why are you filtering on just one of the two tables you are combining?

Re: INLIST

bhca60 — Tue, 24 May 2022 19:08:43 GMT

that was it! i didnt filter on both - thank you thank you!