applying user-defined format to cas in-memory data

[jimleone]

I have a user-defined format that I want to apply to CAS in-memory data.  I've defined the format as:

proc format casfmtlib="casformat";
  picture ymdhms other='%0Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
run;

When I run that, the log shows:

80 proc format casfmtlib="casformat";

NOTE: Both CAS based formats and catalog-based formats will be written. The CAS based formats will be written to the session

MCCOFFERSESS.

81 picture ymdhms other='%0Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);

NOTE: Format library CASFORMAT added. Format search update using parameter APPEND completed.

NOTE: Format YMDHMS has been output.

When I subsequently try to apply the format in a proc cas block, I get a Warning in the log:

"WARNING: The function failed to execute."

I'm trying to apply the format using then "putn" function:

print putn(start_date, 'ymdhms.');

Any advice would be greatly appreciated.

The full proc cas step is:

proc cas;
  table.fileInfo status=rc result=info /
    caslib="my_cas"
    , path="my_log_link_visit_action.sashdat"
    , rowcount=true
  ;
  run;

  if (info.FileInfo[1,5] > 0) then do;
    table.loadTable status=rc result=r /
      caslib="my_cas"
      , path="my_log_link_visit_action.sashdat"
      , casout={
                      caslib="my_cas"
                      , name="my_log_link_visit_action"
                      , replace=true
       }
    ;
end;
run;

if (rc.severity = 0) then do;
  simple.summary result=daterange /
  table={
              caslib="my_cas"
              , name="my_log_link_visit_action"
    }
   , inputs={
                  {name="server_time"}
      }
   , subSet={"MAX"}
    ;

   start_date = daterange.Summary[1,2];

    if (intck('dtday', daterange.Summary[1,2], datetime()) > 30) then
        end_date = intnx('dtday', daterange.Summary[1,2], 30);
    else end_date = datetime();
print '>>>>> ' putn(start_date, 'YMDHMS.');
print '>>>>> ' end_date;

end;
run;

quit;

[sbxkoenk · Accepted solution]

Hello,

After a table is in memory, you can't assign formats to its columns. So, for procedures that run on the CAS server, you use a FORMAT statement with the CASUTIL procedure to assign a format to a column before the table is loaded into memory.

proc casutil;
    format OrderType typefmt.;                                     
    load data=pvbase.orders casout="orders" 
         outcaslib='casuser' replace;
    contents casdata="orders";
quit;

proc freqtab data=casuser.orders;
    tables OrderType;
run;

Here we use a FORMAT statement in PROC CASUTIL to apply the TYPEFMT format to the OrderType column, and then we load pvbase.orders into an in-memory table named orders in the Casuser caslib. The REPLACE option overwrites the orders table if one exists. The PROC FREQTAB step analyzes the formatted values of OrderType, reporting on the three types of orders: In-store, Shipped, and Curbside.

Saving and Reading CAS Format Libraries!

Similar to formats in Base SAS, CAS formats exist only for the duration of the CAS session. So how can you save formats so that they're available in future CAS sessions?
Let me know if you want info on the latter question as well.

Kind regards,

Koen

[MarkDawson]

You can apply a format to table in memory using ALTERTABLE - here I'm loading an Oracle table into memory, and then altering the format for one column

proc casutil ; 
   load incaslib="oracle" casdata="CARS" outcaslib="CASUSER" casout="CARS" replace ; 
   altertable incaslib="casuser" casdata="CARS"  
      columns = { {name= "Cylinders" format="roman." } } ;
quit;

The solution provide previously, works well when loading a table from a Library (DATE=...), but will not work when loading from a CAS Library (INCASELIB=...), such as the Oracle example above.

Using ALTERTABLE should work for any in memory table.

[jimleone]

Thanks, Koen.  I just made this discovery and I appreciate your confirmation and detailed explanation.  I would like to point out, though, that the documentation does not deliver this information as explicitly as you did.

I read this doc yesterday: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/pgmdiff/n0k0n33nje512xn1cl49573ei8k0.htm.  This page states: "It is a best practice to assign a format to a variable before the table is loaded into the server.", which is not quite as explicit as your comment, which to me could be read as "you can't do that."

Anyway...solved!

Thanks.

[sbxkoenk]

Hello @jimleone ,

My information comes from a SAS VIYA 3.5 programming course.

I always do it that way (because I looked at that course in October 2020).

Maybe it changed in VIYA 4 (?), but the doc you mention is SAS VIYA 3.5 so I think this doc should be more explicit indeed.

Kind regards,

Koen