BookmarkSubscribeRSS Feed
VanessaP
Calcite | Level 5

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!

5 REPLIES 5
ballardw
Super User

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.

VanessaP
Calcite | Level 5

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?

Reeza
Super User

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

Cynthia_sas
SAS Super FREQ

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

LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 13789 views
  • 0 likes
  • 5 in conversation