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

Finding No of Days between two dates in SAS VA

sat_lr — Tue, 24 Jun 2014 07:06:53 GMT

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!

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

PeterWijers — Thu, 26 Jun 2014 07:31:55 GMT

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

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

sat_lr — Mon, 07 Jul 2014 06:35:37 GMT

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

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

PeterWijers — Wed, 16 Jul 2014 12:29:49 GMT

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

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

sat_lr — Fri, 18 Jul 2014 11:58:35 GMT

Hi Wejers,

Thanks for your prompt reply. That was extremely helpful.

greetings

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

cov_derek — Thu, 24 Jul 2014 16:21:07 GMT

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.

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

jimd_sas — Thu, 24 Jul 2014 16:45:26 GMT

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.

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

Nascif_SAS — Fri, 01 Aug 2014 19:30:04 GMT

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!

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

Madelyn_SAS — Mon, 04 Aug 2014 14:54:32 GMT

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

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

sat_lr — Tue, 05 Aug 2014 09:38:57 GMT

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

satlr

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

sat_lr — Tue, 05 Aug 2014 10:18:55 GMT

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

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

sam11 — Mon, 29 Aug 2016 14:26:00 GMT

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.

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

HowardNewstadt — Mon, 31 Oct 2016 22:20:57 GMT

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