topic Re: Creating crosstab with overlapping dates in SAS Visual Analytics

Creating crosstab with overlapping dates

Karolek_Oslo — Thu, 24 May 2018 10:43:03 GMT

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

Re: Creating crosstab with overlapping dates

FredrikE — Thu, 24 May 2018 13:15:58 GMT

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

Re: Creating crosstab with overlapping dates

Karolek_Oslo — Thu, 24 May 2018 19:17:28 GMT

Thanks! I thought about that. However I have a lot of rows like the first one where both dates are in the same row.

Re: Creating crosstab with overlapping dates

FredrikE — Fri, 25 May 2018 05:44:15 GMT

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