Issues with time values occur when you run FEDSQL JOIN code in a SAS® Cloud Analytic Services (CAS) session on SAS Viya 3.5.
The following syntax is an example:
create table "target"."table_OUT" {options replace=true } as
select
t1."Op" as "Op",
t1."delta" as "delta",
t2."Operation" as "Operation",
t2."Control_key" as "Control_key"
from
"target"."table_LEFT" t1
inner join
"target"."table_RIGHT" t2
on
t1."Op"=t2."Operation";quit;
In the code, the column "delta" is numeric and has the format "TIME12." The results change the correct value of "38:13:17" to "14:13:17" because "38:13:17" is greater than 24 hours. Instead, the incorrect value result subtracts 24 hours.
To work around this problem, you need to remove the format from the column before you run the FEDSQL JOIN code.
When the FEDSQL JOIN code is generated from a Join transformation in SAS® Data Studio, the Manage columns transformation can be used to remove that format on the Rename Columns riser:
You can then add the format again after the Join transformation is completed:
