BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sat_lr
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Madelyn_SAS
SAS Super FREQ

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

12 REPLIES 12
PeterWijers
Lapis Lazuli | Level 10

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

sat_lr
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

Hi Wejers,

Thanks for your prompt reply. That was extremely helpful.

greetings

cov_derek
Fluorite | Level 6

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
SAS Employee

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

Madelyn_SAS
SAS Super FREQ

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
Calcite | Level 5

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
SAS Employee

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!

sat_lr
Calcite | Level 5

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

satlr

sam11
Calcite | Level 5

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.

 

 

HowardNewstadt
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 18979 views
  • 16 likes
  • 8 in conversation