topic Re: Case When Statement and Null Value in SAS Programming

Case When Statement and Null Value

EKeimig — Thu, 12 Jul 2018 22:19:05 GMT

I am very new to SAS. I am trying to create a Case When Statement.

/* TOT_PREM */

Case When t1.TOT_PREM IS NULL Then 0 Else

(Sum(t1.TOT_PREM)) END AS Total Premium,

I am getting an error message ERROR22-322: Syntax error, expecting one of the following: !,!!,&,*,**,+,-,/,<,<=,<>,=,.,.,.=,?,AND,BETWEEN,

What am I doing wrong?

Re: Case When Statement and Null Value

Reeza — Thu, 12 Jul 2018 22:20:12 GMT

Try MISSING() instead.

SAS doesn't really have a concept of NULL.

when missing(t1.tot_prem) then 0
else ...

@EKeimig wrote:

I am very new to SAS. I am trying to create a Case When Statement.

/* TOT_PREM */

Case When t1.TOT_PREM IS NULL Then 0 Else

(Sum(t1.TOT_PREM)) END AS Total Premium,

I am getting an error message ERROR22-322: Syntax error, expecting one of the following: !,!!,&,*,**,+,-,/,<,<=,<>,=,.,.,.=,?,AND,BETWEEN,

What am I doing wrong?

Re: Case When Statement and Null Value

EKeimig — Thu, 12 Jul 2018 22:28:39 GMT

This is the message I get now.

46 Case When Missing(t1.TOT_PREM) Then 0 Else

47 (Sum(t1.TOT_PREM)) END AS Total Premium,

_______

ERROR 22-322: Syntax error, expecting one of the following: ',', AS, FROM.

Re: Case When Statement and Null Value

Reeza — Thu, 12 Jul 2018 22:46:03 GMT

You don't need to do that at all...SAS ignores missing values in the sum. If you were doing a mean this may be required, but it's not for a SUM().

Try SUM(t1.TOT_PREM) instead.

@EKeimig wrote:

This is the message I get now.

46 Case When Missing(t1.TOT_PREM) Then 0 Else

47 (Sum(t1.TOT_PREM)) END AS Total Premium,

_______

22

ERROR 22-322: Syntax error, expecting one of the following: ',', AS, FROM.

Re: Case When Statement and Null Value

LinusH — Fri, 13 Jul 2018 05:52:10 GMT

IS NULL is valid syntax.
Perhaps it's new variable name that is the problem.
If you want to use names with spaces you have to use quotation and the n literal:
'Total Premium'n

Re: Case When Statement and Null Value

ballardw — Fri, 13 Jul 2018 17:37:58 GMT

When you get an error copy the entire procedure or data step code that generates the error from log including the error messages and paste into a code box opened using the forums {I} icon. This preserves the formatting of the error messages and the indicators that often show what SAS identified as the triggering error.

And example:

244  proc sql;
245     select case when sex='M' then 0
246            else 1 end as Total whatsit
                                   -------
                                   22
ERROR 22-322: Syntax error, expecting one of the following: ',', AS.

247     from sashelp.class;
248  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Notice that I am getting the same error: 22-322 with slightly different "expected" but the underscores show that "whatsit" is out of place.

In this case if I wanted a variable "Total Whatsit" which has a space in the middle then I have to use the construct "Total whatsit"n to indicate a name literal that violates typical variable name rules. OR perhaps I thought there was another variable whatsit I wanted to select but just forgot to place the required comma between variables or expressions in SQL.

Since you did not include the entire code from the log your specific error appears to be in a construct that allows more options than the example I showed but the basic principal applies: one of the expected characters or keywords needs to be before the underscored word or you need to use a different variable name as the result of the case expression. I strongly suggest not using the name literal as the added quotes and required n following get tiresome.

Re: Case When Statement and Null Value

Andy8 — Fri, 24 Jul 2020 18:53:37 GMT

I know this is old, but in case anyone else finds it:

There are a few problems with the code here. One is just extraneous parentheses, one is that there's "Total Premium" has a space in it...but the larger one is the logic of the CASE statement. Remember that CASE looks at a single row and returns a result for each row, so you can't have a SUM *inside* your CASE statement. The order must be reversed, like so:

SUM(CASE WHEN t1.TOT_PREM IS NULL THEN 0 ELSE t1.TOT_PREM END) AS Total_Premium