topic Re: Modify column length of table in a caslib in SAS Viya

Modify column length of table in a caslib

ajulio4 — Wed, 30 Jul 2025 10:00:10 GMT

Hello all,

I want to modyfy the length of a column of my table in a casli without changing the order oaf variables.

Re: Modify column length of table in a caslib

yabwon — Wed, 30 Jul 2025 10:35:14 GMT

how about just:

cas mySession sessopts=(caslib=casuser timeout=3600 locale="en_US" METRICS=TRUE);
options sessref=mySession;

libname myCAS cas caslib=casuser;

/* create sample data */
data myCas.cars(replace=YES);
  set sashelp.cars;
run;

/* metadata before */
ods trace on;
ods output Position=myCas.Varnum(replace=YES);
proc contents data = myCas.cars varnum;
run;
ods trace off;

data _null_;
  set myCas.Varnum END=_E_; 
  
  if _N_ = 1 then call execute(
    'data myCas.cars(replace=YES);retain'
    );
  call execute(Variable);
  if _E_ = 1 then call execute(
    '; Length Model $ 128;' /* change length here */
    || 'set myCas.cars;run;'
    || 'proc delete data=myCas.Varnum; run;'
    );
run;

/* metadata after */
proc contents data = myCas.cars varnum;
run;

Bart

Re: Modify column length of table in a caslib

FreelanceReinh — Wed, 30 Jul 2025 11:25:24 GMT

I don't have CAS available here. Just wondering, wouldn't this work in CAS?

proc sql;
alter table myCas.cars(replace=YES) modify model char(128);
quit;

Re: Modify column length of table in a caslib

yabwon — Wed, 30 Jul 2025 13:20:05 GMT

That was my first attempt, but it gave me:

The same for FedSQL, but different error:

Looks like CAS is not supported format...

Bart

Re: Modify column length of table in a caslib

FreelanceReinh — Wed, 30 Jul 2025 14:30:04 GMT

Thanks, @yabwon, for testing.

It seems to me that restrictions are a big topic when it comes to CAS (or FedSQL for that matter) ...

PROC SQL Statement

Restriction: This procedure is not supported on the CAS server.

Restriction:	This procedure is not supported on the CAS server.

ALTER TABLE Statement

(...)

Restrictions: This statement is not supported in FedSQL programs that run in CAS.

Restrictions:	This statement is not supported in FedSQL programs that run in CAS.

ALTER [COLUMN] <column-definition> [, ...<column-definition>]

specifies to modify the definition of one or more columns.

Data source Amazon Redshift, Aster, DB2 under UNIX and PC, ... [and several others, but NOT including "SAS data set"]

Data source	Amazon Redshift, Aster, DB2 under UNIX and PC, ... [and several others, but NOT including "SAS data set"]

Re: Modify column length of table in a caslib

BrunoMueller — Thu, 31 Jul 2025 10:05:57 GMT

I was wrong about this "The previous example with the DATA step runs on the compute server, all data will be read from CAS and written back to CAS. Depending on the size this may take some time." thanks for the correction @yabwon .

The CAS server supports executing FedSQL, not all statements are supported see https://go.documentation.sas.com/doc/en/pgmsascdc/v_065/casfedsql/p0ipyarz6fe3mvn1hc8ega07irv6.htm for more details.

We can make use of the casting functionality within FedSQL to change the length of char or varchar column. Below is an example program that does this. It is executing on the CAS server so no data transfer between CAS and compute.

/*
 * change length of a char/varchar column
 * one needs to make a copy of an existing table
 *
 * the create table does support the {options replace=true} table option
 * however FedSQL will always create a session scope table
 */
cas sugus sessopts=(caslib="casuser");

/*
 * load some sample data, has session scope
 */
proc casutil;
  load data=sashelp.cars casout="mycars" replace;
run;
quit;

title "columns before";
proc cas;
  action table.columninfo / table="mycars";
  run;
quit;
title;

proc cas;
  /*
    * build column expressions
    * we use the CAST()/:: function to change the length
    */
  action table.columninfo result=cols / table={caslib="casuser" name="mycars"};
  sqlCols=" ";
  do colInfo over cols.columnInfo;
    colName=colInfo.column;

    colExpression=colName;

    /* change as needed */
    if colName="Model" then do;
      colExpression=catx(" ", quote(colName), "::varchar(64) as", quote(colName));
    end;
    /* change as needed */
    if colName="DriveTrain" then do;
      colExpression=catx(" ", quote(colName), ":: varchar(32) as", quote(colName));
    end;

    sqlCols=catx(", ", sqlCols, colExpression);
  end;
  print(note) sqlCols;
  run;
  action table.droptable / caslib="casuser" name="mycars2" quiet=true;
  run;
  /*
    * create complete SQL statement
    * be aware that fedsql will always create a session scope table
    */
  sqlStmt=catx(" " , "create table casuser.mycars2 as select" , sqlCols ,
      "from casuser.mycars;" );
  print(note) sqlStmt;
  action fedsql.execdirect / query=sqlStmt;
  run;
quit;


title "columns after";
proc cas;
    action table.columninfo / table="mycars2";
    run;
quit;
title;

Re: Modify column length of table in a caslib

yabwon — Thu, 31 Jul 2025 09:56:27 GMT

To be clear, the DATA step that changes variable length, i.e., the one that is the result of the CALL EXECUTE() function, does run in CAS:

NOTE: CALL EXECUTE generated line.
1     + data myCas.cars(replace=YES);retain
2     + Make
3     + Model
4     + Type
5     + Origin
6     + DriveTrain
7     + MSRP
8     + Invoice
9     + EngineSize
10    + Cylinders
11    + Horsepower
12    + MPG_City
13    + MPG_Highway
14    + Weight
15    + Wheelbase
16    + Length
17    + ; Length Model $ 128;set myCas.cars;run;
NOTE: Executing action 'table.tableInfo'.
NOTE: Action 'table.tableInfo' used (Total process time):
NOTE:       real time               0.000495 seconds
NOTE:       cpu time                0.000437 seconds (88.28%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  729.16K (0.00%)
NOTE: Executing action 'table.tableInfo'.
NOTE: Action 'table.tableInfo' used (Total process time):
NOTE:       real time               0.000513 seconds
NOTE:       cpu time                0.000477 seconds (92.98%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  725.88K (0.00%)
NOTE: Executing action 'table.columnInfo'.
NOTE: Action 'table.columnInfo' used (Total process time):
NOTE:       real time               0.000587 seconds
NOTE:       cpu time                0.000557 seconds (94.89%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  857.75K (0.00%)
NOTE: Running DATA step in Cloud Analytic Services.
NOTE: Executing action 'sessionProp.getSessOpt'.
NOTE: Action 'sessionProp.getSessOpt' used (Total process time):
NOTE:       real time               0.000173 seconds
NOTE:       cpu time                0.000143 seconds (82.66%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  224.78K (0.00%)
NOTE: Executing action 'sessionProp.setSessOpt'.
NOTE: Action 'sessionProp.setSessOpt' used (Total process time):
NOTE:       real time               0.000576 seconds
NOTE:       cpu time                0.000498 seconds (86.46%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  297.06K (0.00%)
NOTE: The DATA step will run in multiple threads.
NOTE: Executing action 'dataStep.runBinary'.
NOTE: There were 428 observations read from the table CARS in caslib CASUSER(b.jablonski@mini.pw.edu.pl).
NOTE: The table cars in caslib CASUSER(b.jablonski@mini.pw.edu.pl) has 428 observations and 15 variables.
NOTE: Action 'dataStep.runBinary' used (Total process time):
NOTE:       real time               0.097909 seconds
NOTE:       cpu time                0.123906 seconds (126.55%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  42.90M (0.01%)
NOTE: Executing action 'sessionProp.setSessOpt'.
NOTE: Action 'sessionProp.setSessOpt' used (Total process time):
NOTE:       real time               0.000315 seconds
NOTE:       cpu time                0.000281 seconds (89.21%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  307.25K (0.00%)
NOTE: DATA statement used (Total process time):
      real time           0.11 seconds
      cpu time            0.02 seconds

So all data stay in CAS, there is no "read from CAS and written back to CAS", right?

Only the one before, that using myCas.Varnum and calling the CALL EXECUTE() runs in SAS.

100    data _null_;
101      set myCas.Varnum END=_E_;
NOTE: Executing action 'table.tableInfo'.
NOTE: Action 'table.tableInfo' used (Total process time):
NOTE:       real time               0.000566 seconds
NOTE:       cpu time                0.000531 seconds (93.82%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  729.63K (0.00%)
NOTE: Executing action 'table.tableInfo'.
NOTE: Action 'table.tableInfo' used (Total process time):
NOTE:       real time               0.000498 seconds
NOTE:       cpu time                0.000459 seconds (92.17%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  730.38K (0.00%)
NOTE: Executing action 'table.columnInfo'.
NOTE: Action 'table.columnInfo' used (Total process time):
NOTE:       real time               0.000599 seconds
NOTE:       cpu time                0.000568 seconds (94.82%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  853.25K (0.00%)
102    
103      if _N_ = 1 then call execute(
104        'data myCas.cars(replace=YES);retain'
105        );
106      call execute(Variable);
107      if _E_ = 1 then call execute(
108        '; Length Model $ 128;' /* change length here */
109        || 'set myCas.cars;run;'
110        || 'proc delete data=myCas.Varnum; run;'
111        );
112    run;
NOTE: Could not execute DATA step code in Cloud Analytic Services. Running DATA step in the SAS client.
NOTE: Executing action 'table.fetch'.
NOTE: Action 'table.fetch' used (Total process time):
NOTE:       real time               0.001243 seconds
NOTE:       cpu time                0.001210 seconds (97.35%)
NOTE:       total nodes             1 (64 cores)
NOTE:       total memory            503.87G
NOTE:       memory                  1.01M (0.00%)
NOTE: There were 15 observations read from the data set MYCAS.VARNUM.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

Bart

Re: Modify column length of table in a caslib

BrunoMueller — Thu, 31 Jul 2025 10:04:11 GMT

Yes, I forgot about that when input and outout are both from CAS it runs in CAS, thanks for the correction. I will make a change to my post.

Re: Modify column length of table in a caslib

yabwon — Thu, 31 Jul 2025 10:24:42 GMT

@BrunoMueller your code inspired me (thank you!!) to try one more approach. It runs 100% CAS, and it seems pretty to me 🙂

cas mySession sessopts=(caslib=casuser timeout=3600 locale="en_US" METRICS=TRUE);
options sessref=mySession msglevel=i;

libname myCAS cas caslib=casuser;
/* create sample data */
data myCas.cars(replace=YES);
  set sashelp.cars;
run;


proc cas;
  action table.columninfo result=c / table="cars";

  dataStep.runCode status=rc/
    code = 'data casuser.cars(replace=YES);retain '
        || catx(" ",c.ColumnInfo[,"Column"]) 
        || '; Length Model $ 128;' /* change length here */
        || 'set casuser.cars;run;'
  ;
  print rc;
  run;
quit;

All the best

Bart