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

Finding No of Days between two dates in SAS VA

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 85
Accepted Solution

Finding No of Days between two dates in SAS VA

Hi,

How can I find the number of days between two given dates in SAS VA using existing dates in the table?   say for ex find no of days between 12 April, 2014 - 23 June, 2014.

I can use any of the following functionalities to achieve it.

1. New Custom Category

2. New Calculated Item

3. New Aggregated Measure

Pls you could also suggest any other ways to achieve it. Thanks.

good day!


Accepted Solutions
Solution
‎08-04-2014 10:54 AM
SAS Super FREQ
Posts: 381

Re: Finding No of Days between two dates in SAS VA

There is a related  sample that you might be interested in. It uses the TreatAs function to capture the number of days between two dates selected on a range slider

52706 - Capturing the number of days on a range slider in SAS® Visual Analytics Designer

View solution in original post


All Replies
Regular Contributor
Posts: 172

Re: Finding No of Days between two dates in SAS VA

Hi Sat_Ir...

While using VA 6.3 I found that creating a calculated field subtracting the dates form each other, getting the days as difference, does not work.

I found the following solution after creating a new calculated item

Diff_dates.png

Sure it will work for you as well, because this is all about payment days.

Maybe someone else has a better idea....

Greetings  Peter

Frequent Contributor
Posts: 85

Re: Finding No of Days between two dates in SAS VA

thanks wejers. can you pls describe this expression. I see 'day of year' and I think this was made out of VA option day of week or something. 'paymdate' must be the invoice date here. and there is 'date' as well in the expression. little explanation will help me.

greetings.

satlr

Regular Contributor
Posts: 172

Re: Finding No of Days between two dates in SAS VA

Hi satir,

to get de difference between 2 dates you realy need this longer expression. (all done in sas va)

so create a new calculated item with the same formula set-up.

Just replace the "date" and  "Paym.date" with the dates you want to compare.  (those are dates I use in my script)

I found that while only using dayofyear command, you can not check the days difference over a year switch.

So while checking the year change between the dates multiplied with 365 and adding the days difference, you get it right.

greetings

Frequent Contributor
Posts: 85

Re: Finding No of Days between two dates in SAS VA

Hi Wejers,

Thanks for your prompt reply. That was extremely helpful.

greetings

Contributor
Posts: 23

Re: Finding No of Days between two dates in SAS VA

Maybe this is a shot in the dark, but have you tried using the DATDIF() function? It's my understanding that you can use SAS functions in VA.

SAS Employee
Posts: 1

Re: Finding No of Days between two dates in SAS VA

Create a calculated item and then Use function TreatAs to convert dates into numbers and subtract them.  If they are in DateTime then don't forget to use DatePart.  If you want to do Today - StartDate then you could use TreatAs DatePart ( Now() ).

If you want to do EndDate - StartDate OR Today - StartDate (if EndDate is missing), you could do that too using the Boolean Operator first.

treatas.jpgtreatas2.jpg

Solution
‎08-04-2014 10:54 AM
SAS Super FREQ
Posts: 381

Re: Finding No of Days between two dates in SAS VA

There is a related  sample that you might be interested in. It uses the TreatAs function to capture the number of days between two dates selected on a range slider

52706 - Capturing the number of days on a range slider in SAS® Visual Analytics Designer

Frequent Contributor
Posts: 85

Re: Finding No of Days between two dates in SAS VA

Hi BIC_USER...that was extremely useful. I just got my o/p. perhaps wanted to know, the range days always gives the same no of days when I change with other product dimensions or customer dimensions? any suggestions would help.

greetings

satlr

SAS Employee
Posts: 11

Re: Finding No of Days between two dates in SAS VA

A bit of a tangent to the original question, but I think it is valuable to share. I see all the responses showing how to create expressions to address the original question - but they are being shared as screenshots. Did you know you can also share them in their Text version - which not only you can modify directly, as if you were authoring code or SQL, but also copy and past between VA instances?

Here is an example. I have been working on the expression below, which is similar to the ones above:

query_visual.png

But now I will switch to its Text version. Just click on the tab at the top right of the editing area:

query_text.png

Next, right-click on it, select all and copy to the clipboard:

query_copy.png

And here is the final result, as text:

( 'Open+'n NotIn ('CLOSED') ) AND ( ( Year(DatePart(Now())) - Year('Date Opened'n) ) <= 'YearHorizon'p )

If your dataset has matching columns, you can re-create my expression simply by going into a new expression editor and pasting the text above in the Text editing area. Or you can paste it and change variable names to match your data.

I even keep a file with my favorite expressions that I re-use in different reports and explorations.

Give it a try!

Frequent Contributor
Posts: 85

Re: Finding No of Days between two dates in SAS VA

thanks nasif...that was pretty good approach. will give a try! greetings.

satlr

Learner
Posts: 1

Re: Finding No of Days between two dates in SAS VA

[ Edited ]

I would like to share another work around to solve the issue which is quite affective:

 

Problem statement: Creating buckets on difference of two dates in SAS VA 

 

Assuming two date columns

1. Date(1)

2. Now 2 (1) (Current Date) 

 

Steps to follow : 

1. Under "Property > Format " section of Date(1) and Now 2 (1) select format to be " Day of Year"

2.Create calculated column "Difference in Date"

 Calculate Item

3. Again convert format of "Difference in Date" to "Day of year"

4. Create Buckets using Custom category options.

 

 

New Contributor
Posts: 4

Re: Finding No of Days between two dates in SAS VA

I'm finding that this method does not account for days difference greater than 365, e.g., June 1, 2014 to May 31, 2016.

Post a Question
Discussion Stats
  • 12 replies
  • 5558 views
  • 15 likes
  • 8 in conversation