Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Creating crosstab with overlapping dates

Reply
New Contributor
Posts: 2

Creating crosstab with overlapping dates

I have a dataset looking something like this:

 

CustomerFrom dateTo datePremiumNewExitDate_newDate_exit
109.jun.1631DEC2016399511201606201612
411.jun.1411.jun.15303210201406 
411.jun.1515.jul.15370301 201507
528.sep.1428.sep.15507200  

 

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 newPremium exit
20140630320
20150703703
20160639950
20161203995

 

I have created a New calculated item:

Skjermbilde.PNG

 

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

 

 

PROC Star
Posts: 391

Re: Creating crosstab with overlapping dates

Posted in reply to Karolek_Oslo

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

New Contributor
Posts: 2

Re: Creating crosstab with overlapping dates

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

PROC Star
Posts: 391

Re: Creating crosstab with overlapping dates

Posted in reply to Karolek_Oslo

Ah, ok, then you need to fix it in the data before you load it to VA Smiley Happy

Easy to do in data step using multiple outputs.

//Fredrik

Ask a Question
Discussion stats
  • 3 replies
  • 186 views
  • 0 likes
  • 2 in conversation