A data _null_ step shows that my approach works without the quoting issue:
data _null_;
_statistic_="Average-Quantile";
_arg1_=.;
want=compress(cats(_statistic_, _arg1_),".-");
put want=;
run;
But when I use the compress function within the call execute construct, I cannot make it work.
I don't know how to pass the arguments "-." to the compress function. I seems like I have to quote the quote.
"'"
"""""
'||"'"
???
I'm aware that my code below cannot be reproduced without data and macro variables, ...
But it works when I replace the error producing line (compress) with a hard-coded value.
data _null_;
set xdata2;
call execute('
%let varlist="";
proc cas;
action columninfo result=r /table={name="'|| strip(name) || '", caslib="ORACASLIB"};
empty = 0;
if (r.columnInfo.nrows <= 0) then do;
empty = 1;
rc = symput("emptyTable", (string)empty);
end;
else do;
columns = "";
do i = 1 to r.columninfo.nrows;
if ^index(UPCASE(r["columninfo"][i,4]), "CHAR") or 1=2 THEN do;
symput("col", quote(r[1,i].column));
columns = columns||", "||symget("col");
end;
end;
symput("varlist", substrn(columns,2,length(columns)-1));
end;
run;
quit;
proc cas;
dataPreprocess.rustats /
table={name="'|| strip(name) || '", caslib="ORACASLIB"}, inputs={' || symget(varlist) || '},
requestpackages={{allLocations=TRUE, allSkewnesses=TRUE}},
casoutstats={name="mydata_rustats",caslib="casuser", replace=true};
run;
transpose.transpose /
table={caslib="casuser", name="mydata_rustats", groupby={"_variable_"},
computedvars={{name="newid",format="$20."}},
computedvarsprogram="newid=' || compress(cats(_statistic_, _arg1_),".-") ||';"},
transpose={"_value_"}, id={"newid"}, casOut={caslib="casuser", name="'|| catx("_", strip(name)) || '",
replace=true};
run;
quit;');
run;
CALL EXECUTE executes some macro code immediately. This may be the problem. There is also an error in your code, which is this:
symput("varlist", substrn(columns,2,length(columns)-1));
It is CALL SYMPUT, not just SYMPUT.
There may also be some problems with nested quotes in your code, I am not sure. I generally do not use CALL EXECUTE for such solutions, I think it works better to generate a SAS program and %INCLUDE that. That way, you can first look at the code, to see if it looks all right, then try and submit it. If it works OK, you can put the %INCLUDE (may want to use the /SOURCE2 option) in your original code.
Something like this:
filename tempsas temp; /* temporary file, gets deleted when freed or reallocated */
data _null_;
set xdata2;
file tempsas;
put
'%let varlist="";'/
'proc cas;'/
' action columninfo result=r /table={' name= ', caslib="ORACASLIB"};'/
' empty = 0;' /
/* etcetera */
'quit;';
run;
You will then have a file containing all the code generated. Open it, and see if it looks OK. If it does, try submitting the code down to the first "quit;" statement, and see if it generates the result you want. Then try submitting the rest of the file, and see if that also works.
CALL EXECUTE executes some macro code immediately. This may be the problem. There is also an error in your code, which is this:
symput("varlist", substrn(columns,2,length(columns)-1));
It is CALL SYMPUT, not just SYMPUT.
There may also be some problems with nested quotes in your code, I am not sure. I generally do not use CALL EXECUTE for such solutions, I think it works better to generate a SAS program and %INCLUDE that. That way, you can first look at the code, to see if it looks all right, then try and submit it. If it works OK, you can put the %INCLUDE (may want to use the /SOURCE2 option) in your original code.
Something like this:
filename tempsas temp; /* temporary file, gets deleted when freed or reallocated */
data _null_;
set xdata2;
file tempsas;
put
'%let varlist="";'/
'proc cas;'/
' action columninfo result=r /table={' name= ', caslib="ORACASLIB"};'/
' empty = 0;' /
/* etcetera */
'quit;';
run;
You will then have a file containing all the code generated. Open it, and see if it looks OK. If it does, try submitting the code down to the first "quit;" statement, and see if it generates the result you want. Then try submitting the rest of the file, and see if that also works.
I can use it. But I don't know how to inspect / see the temp file...
%include tempsas / source2;
if you have temporary file use:
filename tempsas list;
to see its location in the log;
Bart
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.