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

In VA 6.3 I am trying to calculate the difference between 2 datetime stamps - but only when both are not missing.  Currently, I am getting the missing date as Jan 1 1960, and a very large # of days for my calculation which I don't want.  I want to NOT display the Missing value as January 1 1960 12:00:00 AM in the PREV ETR D/T column and NOT have a result for the calculation if one of the dates is missing in my data source.

 

2017-05-14_10-37-35.jpg

These are my calculated items and calculations so far for the example above.

PREV ETR D/T column:

Parse('PREV ETR'n, 'ANYDTDTM19.')

 

DIFF_ETR_CHANGED_TIME_PREV_ETR column:

TreatAs(_Number_, ( TreatAs(_Number_, 'TIME OF ETR CHANGE  BY OP/SVCMAN'n) - TreatAs(_Number_, 'PREV ETR D/T'n) ))

 

I have tried to put an IF...THEN..ELSE around the calculation and only have it executed if the PREV ETR D/T is not missing - but I can't seem to get that to work.

 

Any help is greatly appreciated.

 

Thanks!

 

 

 

 

 


2017-05-14_10-37-35.jpg
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

SAS typically stores datetimes as the number of seconds from January 1, 1960. In Base SAS you would use DATEPART to extract the date protion of the variable and then subtract the two variables. You likely need something similar in VA. 

I would use COALESCE in SAS VA to test the missing and assign it to the value you expect or want. 

 

 

View solution in original post

3 REPLIES 3
art297
Opal | Level 21

I'm not familiar with SAS VA but, from the snapshot you posted, it looks like the datetimes you are considering as null or missing, actually have a value of 0. Have you tried your if then logic checking whether the value is null, missing and/or 0?

 

Art, CEO, AnalystFinder.com

 

Tony_V
Calcite | Level 5

Art

Thanks.  They definitely are Missing when viewed in SAS VA Data Explorer.  Also, when I view my raw data source in SQL developer - they say NULL.  I am forcing them to NULL with a SQL CASE statement in the Oracle view that is the source for the SAS VA LASR table.

 

I rebuilt some of the calculations part of the report from scratch (originally I just changed the data source of an existing report) and now I seem to get the result I am looking for.  I get a - (dash) when the value is missing and in the calculation it also gives me a - since I assume it can't do the calculation of x-y if one of the values is missing.

 

So, it seems I may have something not quiet right in my current version of the report.

 

I will keep looking as I don't really want to rebuild it all from scratch again.

 

Thanks again.

 

 

Reeza
Super User

SAS typically stores datetimes as the number of seconds from January 1, 1960. In Base SAS you would use DATEPART to extract the date protion of the variable and then subtract the two variables. You likely need something similar in VA. 

I would use COALESCE in SAS VA to test the missing and assign it to the value you expect or want. 

 

 

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
  • 3 replies
  • 4897 views
  • 0 likes
  • 3 in conversation