Case When Statement and Null Value

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Case When Statement and Null Value

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?


Accepted Solutions
Solution
Friday
Super User
Posts: 13,566

Re: Case When Statement and Null Value

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.

View solution in original post


All Replies
Super User
Posts: 23,754

Re: Case When Statement and Null Value

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?


 

New Contributor
Posts: 2

Re: Case When Statement and Null Value

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.

Super User
Posts: 23,754

Re: Case When Statement and Null Value

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.


 

Super User
Posts: 5,884

Re: Case When Statement and Null Value

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
Data never sleeps
Solution
Friday
Super User
Posts: 13,566

Re: Case When Statement and Null Value

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 79 views
  • 2 likes
  • 4 in conversation