topic Re: Converting SQL code in to SAS 9.4 in SAS Programming

Converting SQL code in to SAS 9.4

15GreyGoose — Tue, 02 Apr 2019 19:39:06 GMT

Hi!

I'm trying to re-work this Case When statement from SQL for use in SAS 9.4.

SQL / Example: Sum(Case When IsNull(mkt_name,'') not in ('TBC', 'Web') then 1 else 0 end) as Cnt_Received_Branch

Here is what I think it should read:

SAS / Solution: Sum(Case When mkt_name Is Null and NOT In ('TBC','Web') then 1 else 0 end) as Cnt_Received

However it is erroring out. Any suggestions would be much appreciated. I simply need the SAS equivalent for this:

SQL / Example: Sum(Case When IsNull(mkt_name,'') not in ('TBC', 'Web') then 1 else 0 end) as Cnt_Received_Branch

Thank you SAS community,

Andrew

Re: Converting SQL code in to SAS 9.4

PaigeMiller — Tue, 02 Apr 2019 19:48:14 GMT

When mkt_name is null, then it also is not in ('TBC','Web')

So your query is not doing something reasonable, and probably needs to be modified.

Nevertheless, the SAS syntax is

Case When mkt_name Is Null and mkt_name NOT In ('TBC','Web')

Re: Converting SQL code in to SAS 9.4

Reeza — Tue, 02 Apr 2019 19:51:51 GMT

ISNULL() Is more like COALESCE() but not sure exactly how that's working here, but SQL dbs do differentiate between null and missing, whereas SAS does not, so you may want to clarify the logic around that function.

Re: Converting SQL code in to SAS 9.4

Astounding — Tue, 02 Apr 2019 20:05:05 GMT

If this gives you the correct answer, it would certainly be easier to understand:

Sum(Case When mkt_name in ('TBC', 'Web') then 0 else 1 end) as Cnt_Received_Branch