topic Re: calculated keyword in proc fedsql in SAS Viya

calculated keyword in proc fedsql

m_pellegrini — Fri, 31 Jan 2025 09:26:00 GMT

Good morning everyone.

I'm trying to use sas Viya with the sas demo. In the documentation I haven't found many examples or explanations for some key topics, such as the conversion of many proc sql codes into proc fedsql. For example, I haven't been able to convert the CALCULATED clause in any way. I'm reporting the example of proc sql and the related conversion attempt with proc fedql

cas mysess;
caslib mycas path="/shared/viya/homes/sasdemo/c_cas";
libname mycas cas;

caslib _all_ assign;

data mycas.class;
set sashelp.class;
run;

/*PROC SQL*/
proc sql;
create table test_sql as
select name,
       weight,
       height,
       sum(height) as sum_height
   from sashelp.class
group by name
having calculated sum_height > 50;
quit;

 
/*FEDSQL VIYA*/
proc fedsql sessref=mysess;
create table mycas.test_fed as
select name,
       weight,
       height,
       sum(height) as sum_height
   from mycas.class
group by name
having  sum(height) > 50;
quit;

Re: calculated keyword in proc fedsql

quickbluefish — Fri, 31 Jan 2025 11:22:16 GMT

Couple of things. First, in your PROC SQL example, you do NOT need the CALCULATED keyword in this case. Just saying "HAVING sum_height>50" works fine. CALCULATED is used in situations where you want to reference a variable you created in the SELECT clause somewhere else in the same SELECT clause.

Also, in the PROC SQL step and in general in SQL, having variables in your SELECT that do not also appear in your GROUP BY and that are not being summarized (with a function like SUM, MEAN, MAX, etc.), is not usually what you want (sometimes, but not usually). For ex., here, you have name, weight, and height listed in your SELECT, but only name appears in the group by. I would just recommend that you think carefully about what you're trying to get with this result.

For the FEDSQL part, the way you have it (with regard to the HAVING clause) is correct - in general, "real" SQL does not allow you to reference a variable you derived in the SELECT clause in the HAVING clause. This is kind of a convenience feature built into PROC SQL, which is a much more forgiving form of SQL.

Hope that helps.

Re: calculated keyword in proc fedsql

m_pellegrini — Fri, 31 Jan 2025 11:48:39 GMT

I read somewhere that the CALCULATED clause is not supported on PROC FEDSQL, and I simply wanted to verify this fact at the syntax level, finding a valid alternative. In the code that I indicated to me it goes into error in sas Viya, I report the error obtained in the log:

80   /*FEDSQL*/
81   proc fedsql sessref=mysess;
82   create table test_fed as
83   select name,
84          weight,
85          height,
86          sum(height) as sum_height
87      from mycas.class
88   group by name
89   having calculated sum_height > 50;
ERROR: Syntax error at or near "SUM_HEIGHT"
ERROR: The action stopped due to errors.
ERROR: The FedSQL action was not successful.
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
90   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FEDSQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Re: calculated keyword in proc fedsql

quickbluefish — Fri, 31 Jan 2025 13:39:28 GMT

Right - and the way you currently have your code (in your original post) for FEDSQL is correct:
...HAVING sum(height)>50
...this would also be the case in other versions of true SQL (e.g., T-SQL, PL-SQL)