I have a dataset looking something like this:
Customer | From date | To date | Premium | New | Exit | Date_new | Date_exit |
1 | 09.jun.16 | 31DEC2016 | 3995 | 1 | 1 | 201606 | 201612 |
4 | 11.jun.14 | 11.jun.15 | 3032 | 1 | 0 | 201406 | |
4 | 11.jun.15 | 15.jul.15 | 3703 | 0 | 1 | 201507 | |
5 | 28.sep.14 | 28.sep.15 | 5072 | 0 | 0 |
It shows the Insurance Premium for each customer. A flag is used to indicate whether the Premium is new or the custmer is leaving (exit).
I would like to create a Crosstab in SAS VA with to columns with sum of Premium New and Premium exit for each year/month:
Premium new | Premium exit | |
201406 | 3032 | 0 |
201507 | 0 | 3703 |
201606 | 3995 | 0 |
201612 | 0 | 3995 |
I have created a New calculated item:
It says: IF (EXIT = 1) RETURN PREMIUM else Missing. And equivalent for New Premium.
Is there a way in SA VA that I can "merge" the two date columns (date_new and date_exit) from my dataset into one date column in SAS VA (Version 7.4)? Or do I have to further prepare my data using SAS code?
Best regards
Karolina
You should be able to check if date_new is missing and if so choose date_exit, otherwise choose date_new?!
IF Missing(date_new)
return date_exit
else date_new
//Fredrik
Thanks! I thought about that. However I have a lot of rows like the first one where both dates are in the same row.
Ah, ok, then you need to fix it in the data before you load it to VA 🙂
Easy to do in data step using multiple outputs.
//Fredrik
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.