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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.