@LinusH @Patrick @Stu_SAS Thanks to your advices.
I haven't tried the hash option, because I refrain from learning it 🙂
The best result in terms of speed comes from defining a customized action that, within the builtin action set, combines an aggregate action and a fedsql.
I share the code. Some variables of the right joined table in my problem statement are still missing, but I don't expect big performance issues.
I would say that I cut the execution time by factor 0.2.
cas mySession sessopts=(caslib="casuser");
/*create an action set called tblMakers*/
%let _sv_quotelenmax=%sysfunc(getoption(quotelenmax));
options noquotelenmax;
proc cas;
builtins.defineActionSet /
name="aggregator"
type="CASL"
actions={
{
name="aggr"
desc="Return aggregation"
parms={
{name="caslib" type="string" required=TRUE}
{name="table" type="string" required=TRUE}
{name="var1" type="string" required=TRUE}
{name="type1" type="string" required=TRUE}
{name="type2" type="string" required=false}
{name="gr1" type="string" required=TRUE}
{name="gr2" type="string" required=false}
{name="clause" type="string" required=false}
}
definition=
"
if exists('Clause') then whereClause=catx(' ','where',whereClause);
else Clause='1=1';
aggregation.aggregate /
table={ name=table,
caslib=caslib,
where=clause,
groupby={gr1, gr2}
},
casout={name='_have', replace=true}
varspecs={
{summarySubset={type1}, Name='measurement', columnnames={'_' || type1 || '_'}},
{summarySubset={type2}, Name='measurement', columnnames={'_' || type2 || '_'}}
},
savegroupbyraw=true, savegroupbyformat=false, raw=false;
"
}
{
name="joiner"
parms={ {name="tblName" type="STRING" required=True}
{name="table" type="STRING" required=True}
{name="caslib" type="STRING" required=True}
{name="type1" type="string" required=false}
{name="type2" type="string" required=false}
{name="gr1" type="string" required=TRUE}
{name="gr2" type="string" required=false}
{name="whereClause" type="STRING" required=False}
}
definition=
" if exists('whereClause') then whereClause=catx(' ','where',whereClause);
else whereClause='';
fedSQL.execDirect / query='create table casuser.'||tblName||' {options replace=true} as
select a.*, _'||type1||'_, _'||type2||'_
from '||caslib||'.'||table||' a inner join CASUSER._have b on a.'||gr1||'=b.'||gr1||' and a.'||gr2||'=b.'||gr2||' ';
"
}
};
run;
options &_sv_quotelenmax;
%let t1=std;
%let t2=mean;
%let g1=recipe;
%let g2=_name_;
/* run the tblMakers.tableCreateParam Action */
proc cas;
aggregator.aggr / table="TTT" caslib="public" var1="measurement" type1="&t1" type2="&t2" gr1="&g1" gr2="&g2" clause="datepart(datetime) > '13feb2025'd" ;
aggregator.joiner / tblName="want3" table="TTT" type1="&t1" type2="&t2" gr1="&g1" gr2="&g2" caslib="public";
run;
PROC CAS;
table.fetch / table={ caslib="casuser" name="want3", VARS={"&g1", "&g2", "_&t1._", "_&t2._"}} to=5000;
RUN;
... View more
