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;
