05-14-2017 11:32 AM
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.
These are my calculated items and calculations so far for the example above.
PREV ETR D/T column:
Parse('PREV ETR'n, 'ANYDTDTM19.')
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.
05-14-2017 12:02 PM
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
05-14-2017 02:49 PM
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.
05-14-2017 03:26 PM
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.