topic Re: Proc SQL UPDATE not working with IN keyword in SAS Programming

Proc SQL UPDATE not working with IN keyword

gmuersch — Tue, 27 Aug 2019 21:03:52 GMT

Hey everyone,

I have a table, TEST, and I want to update the TEST.status = "submitted" if TEST.ID exists in another table I have of IDs

I wrote this code below in proc sql but for some reason the IN keyword wont highlight and I am not getting the expected result

proc sql;

update work.test
set status = "submitted"
where id in
(select ID from work.list_of_IDs);

quit;

It runs without errors but I get the note that no rows have been updated.

If anyone knows what is going wrong or can offer a solution I would really appreciate it, I have been struggling with this for some time now.

Re: Proc SQL UPDATE not working with IN keyword

Tom — Tue, 27 Aug 2019 21:17:19 GMT

"highlight"? Are you talking about something the editor is doing to give you a little help it finding typos? I wouldn't worry about whether the editor knows how to highlight your code or not.

Test whether there are in fact any observations that need updating.

proc sql;
select count(*) as number_to_update 
from work.test
where id in (select ID from work.list_of_IDs)
;
quit;

Perhaps none of the values of ID in LIST_OF_IDS match the values of ID in TEST.

Re: Proc SQL UPDATE not working with IN keyword

gmuersch — Tue, 27 Aug 2019 21:20:50 GMT

Yeah I actually realized my list_of_IDs was incorrect, some leading zeros were truncated in excel and that was causing the issue. Thanks for the help!!

Re: Proc SQL UPDATE not working with IN keyword

PGStats — Tue, 27 Aug 2019 21:21:35 GMT

It should work. Make sure the IDs are exactly the same in both tables. Watch for leading spaces or zeros if the IDs are character, or fields that differ but look the same only because of formats.

Re: Proc SQL UPDATE not working with IN keyword

Tom — Tue, 27 Aug 2019 21:22:39 GMT

@gmuersch wrote:

Yeah I actually realized my list_of_IDs was incorrect, some leading zeros were truncated in excel and that was causing the issue. Thanks for the help!!

Excel is where data goes to die. Or at least get managled into unrecognizable junk.

Re: Proc SQL UPDATE not working with IN keyword

ballardw — Tue, 27 Aug 2019 23:32:05 GMT

"IN" used that way is an operator and basically none of the operators like: + * - / OR AND NOT > < are highlighted. So that isn't to be worried about at all. Depending on SAS modules you'll find many key elements that are not highlighted and sometimes ones reported as "unknown keywords" just because the Editor hasn't been told to see them yet.

Small example data of your values may provide some insight. Only provide examples of the variables used.

Re: Proc SQL UPDATE not working with IN keyword

Reeza — Wed, 28 Aug 2019 02:33:52 GMT

Do you have an Excel file or a CSV file?

@gmuersch wrote:

Yeah I actually realized my list_of_IDs was incorrect, some leading zeros were truncated in excel and that was causing the issue. Thanks for the help!!