Finding No of Days between two dates in SAS VA

[sat_lr]

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!

[PeterWijers]

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

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

Maybe someone else has a better idea....

Greetings  Peter

[sat_lr]

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

[PeterWijers]

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

[sat_lr]

Hi Wejers,

Thanks for your prompt reply. That was extremely helpful.

greetings

[cov_derek]

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.

[jimd_sas]

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.

[Madelyn_SAS · Accepted solution]

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

[sat_lr]

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

[Nascif_SAS]

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:

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

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

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!

[sat_lr]

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

satlr

[sam11]

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"

 

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

4. Create Buckets using Custom category options.

[HowardNewstadt]

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