Using a date in a case statement

Reply
New Contributor
Posts: 2

Using a date in a case statement

I'm creating a case statement saying  "Case when X = Y then output a date field else Null".   

When using null, 'm getting error messages saying that its not the same data type.  I've changed it to a dummy date (01Jan1970) and the error doesn't seem to recognise the date.  I'm getting a syntax error that seems to expect an operator.

 

Can anyone help?

 

Thank you!

Grand Advisor
Posts: 10,251

Re: Using a date in a case statement

Please show the code you are using and error messages from the log in a code box opened with the forum {i} menu icon.

Also indicate if this is being run as pass through to an external database system.

SAS does not actually understand "null". Missing values are indicated with a . for numeric or blank ("") for character variables. Likely you tried to assign character to numeric in some flavor.

New Contributor
Posts: 2

Re: Using a date in a case statement

I've got a table with a person reference and the first date they went on a holiday, I want to count how many times they have been on another holiday in the 365 days since their first holiday

So I've tried to bring back all the dates they have been on a holiday in the last 365 days with the following code:

Case
When t3.BOOKING_STATUS_GROUP_NAME="Firm" and t3.BREAK_TYPE in ("AD","FA") and Gross_Cost>1 and Gross_Cost<10000 and DATDIF( t1.MIN_of_ARRIVAL_DATE,t3.ARRIVAL_DATE, 'Actual') BETWEEN 1 AND 365
Then t3.ARRIVAL_DATE
Else 0
End

 

Can you suggest a better way to do this?

Grand Advisor
Posts: 17,464

Re: Using a date in a case statement


VanessaP wrote:

I've got a table with a person reference and the first date they went on a holiday, I want to count how many times they have been on another holiday in the 365 days since their first holiday

So I've tried to bring back all the dates they have been on a holiday in the last 365 days with the following code:

Case
When t3.BOOKING_STATUS_GROUP_NAME="Firm" and t3.BREAK_TYPE in ("AD","FA") and Gross_Cost>1 and Gross_Cost<10000 and DATDIF( t1.MIN_of_ARRIVAL_DATE,t3.ARRIVAL_DATE, 'Actual') BETWEEN 1 AND 365
Then t3.ARRIVAL_DATE
Else 0
End

 

Can you suggest a better way to do this?


Post sample data. It looks like the logic is more complex than just a straight 'went on vacation in previous year' as well. 

SAS Super FREQ
Posts: 8,721

Re: Using a date in a case statement

HI:

  It is not clear where you're using your CASE expression. Is it in a PROC SQL? What is the exact code that you are trying and the log that shows the EXACT error message.

 

  What do you mean by "output a date field" -- are you trying to make a date variable and assign a value?

 

  What do you mean by "error doesn't recognize the date" -- did you try something like this:

  newdate = 01Jan1970;

or did you try something like this

newdate = '01Jan1970'd;

 

  Remember that a date variable is just a SAS numeric variable that is holding the number of days since Jan 1, 1960 like this:

dates_offset_from_0.png

 

  So Dec 27, 1959 would be internally stored as -5 and Jan 9, 1960 would be internally stored as 9. But you don't want to have to figure out what the number is for July 4, 2017, so if you did something like this:

what_is_this_date = '04Jul2017'd;

then SAS would take the date constant in the expression and show you a value of 21004 as shown below:

what_is_date.png

 

  Without seeing the code you tried or the error message you got, it's hard to provide a constructive answer. Here's an example that illustrates making a date variable (BIRTHDAY) in either a DATA step or PROC SQL for only certain values of the NAME variable.

 

cynthia

 

if_case.png

Esteemed Advisor
Posts: 5,202

Re: Using a date in a case statement

I don't have SAS at my fingertips but a suspect that NULL is an operator you can do logic with, but is not a constant that can be assigned.
So if you want to assign a NULL (or MISSING) use period <.> in the else clause given that you are using a numerical SAS date as your target column.
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 92 views
  • 0 likes
  • 5 in conversation