topic Re: Using a date in a case statement in SAS Programming

Using a date in a case statement

VanessaP — Wed, 12 Jul 2017 14:15:41 GMT

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!

Re: Using a date in a case statement

ballardw — Wed, 12 Jul 2017 14:23:55 GMT

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.

Re: Using a date in a case statement

Cynthia_sas — Wed, 12 Jul 2017 14:45:22 GMT

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:

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:

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

Re: Using a date in a case statement

VanessaP — Wed, 12 Jul 2017 14:52:23 GMT

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?

Re: Using a date in a case statement

Reeza — Wed, 12 Jul 2017 15:04:25 GMT

@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.

Re: Using a date in a case statement

LinusH — Wed, 12 Jul 2017 15:20:00 GMT

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.