problem with time duration in caslib table

[ajulio4]

Please can someone help me. I have SAS 9 table with colonne TPS whixh specify the duration. Eg i can have in this variable modality like 30:22:00. After chargin this table in my CASLIB, for the same record  this variable shows 6:22:00.

Seems like SAS ignore the 24 hours . After 24hours  SAS set to null the duration.

How can i find issue to this problem ?

Thks

[ballardw]

What format was assigned to the variable? It sounds like you have an unwanted TimeAMPM type format assigned which would display 30:22:00 as 06:22:00 AM.

Perhaps changing to the format to a simple TIME format would be what is needed.

[Tom]

Not sure about CAS but in normal SAS that could just mean you have used TOD instead of TIME as the display format.

5    data test;
6      duration = '30:22:00't ;
7      put duration= time12. duration=tod8.;
8    run;

duration=30:22:00 duration=06:22:00

[Patrick]

It's the same with CAS.

data work.demo_compute;
  format dt_1 tod. dt_2 time.;
  dt_1='30:22:00't ;
  dt_2='30:22:00't ;
run;

title 'demo_compute';
proc print data=work.demo_compute;
run;

data casuser.demo_cas;
  set work.have;
run;

title 'demo_cas';
proc print data=casuser.demo_cas;
run;

proc casutil;
  alterTable
    casdata="demo_cas"
    columns={{name="dt_1" format="time."}}
  ;
quit;

title 'demo_cas after changing format'; 
proc print data=casuser.demo_cas;
run;

[ajulio4]

here is my problem :

data montest;
format tp1 time8. ;
tp1= 109320;
run;

proc fedsql;
create table montest2 as select * from montest ;
quit;

[Patrick]

@ajulio4 Using your code which executes under compute (not CAS) and creates tables in work (not CAS) I can replicate what you describe. ...and I can also replicate what you describe under SAS9.4M8.

Here the code I executed both under a recent Viya environment and under a local laptop install of SAS9.4M8

proc datasets lib=work nolist nowarn;
  delete montest montest2;
quit;

data montest;
  format tp1 time8. ;
  tp1= 109320;
  tp2= 109320;
run;

proc fedsql;
  create table montest2 as select * from montest ;
quit;

title 'montest';
proc print data=montest;
  format tp1 tp2 best32.;
run;
title 'montest2';
proc print data=montest2;
  format tp1 tp2 best32.;
run;
title;

As you can see the actual internal value got changed so it's not a simple display issue.

It looks to my like FedSQL doesn't deal correctly with a SAS source column that's numeric with a time format attached. Given that FedSQL then actually changes the internal value but only writes a Note (=no error condition) I consider this a bug and suggest you raise it with SAS Tech Support.

NOTE: BASE driver, creation of a TIME column has been requested, but is not supported by the BASE driver. A DOUBLE PRECISION column 
      has been created instead. A format has been associated with each column.

If your source and target tables are both SAS tables (files) then just don't use FedSQL but Proc SQL or a data step instead. 

[Tom]

Is there some reason to use FEDSQL?  It seems to be the problem.

If you don't attach the TIME format you won't have the problem. (you can always attach it when you are actually producing output).  Or you can attach the DATETIME format instead.  FEDSQL does not appear to modify those values.

202  data _null_;
203   set montest;
204   put (_all_) (=) / (_all_) (=:best32.);
205  run;

tp1=30:22:00 tp2=02JAN1960:06:22:00 tp3=109320
tp1=109320 tp2=109320 tp3=109320
NOTE: There were 1 observations read from the data set WORK.MONTEST.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


206
207  data _null_;
208   set montest2;
209   put (_all_) (=) / (_all_) (=:best32.);
210  run;

tp1=6:22:00 tp2=02JAN1960:06:22:00 tp3=109320
tp1=22920 tp2=109320 tp3=109320
NOTE: There were 1 observations read from the data set WORK.MONTEST2.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds