- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi people
The below is causing this error:
ERROR: Column "AAAA.PRECISIONS" must be GROUPed or used in an aggregate function
ERROR: The action stopped due to errors.
ERROR: The FedSQL action was not successful.
No idea what is generating the error in the final aggreagation, as all the sub-queries work fine (tested)
proc fedsql sessref=casauto;
create table casuser.test as
select
mean(precisions) as mean_precision,
median(precisions) as median_precision
from
(
select
aaa.person_id,
count(distinct(aaa.gms_deliverable_id))/5 as precisions
from
(
select
a.person_id,
b.gms_deliverable_id
from
user_recc_open a
left join
recc_test_open b
on a.person_id=b.person_id
and inputn(a.gms_deliverable_id,'best.')=b.gms_deliverable_id
) aaa
group by aaa.person_id
) aaaa
;
quit;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
There is no aggregate MEDIAN() function in FEDSQL.
Since the MEDIAN of a single value is just that value using it that way in your query makes no sense. If you replace it with its equivalent
precisions as median_precision
It is clearer why FEDSQL is complaining. Unlke PROC SQL in FEDSQL SAS will not automatically remerge aggregate results back onto all of the individual rows.
If you want to calculate the median over the whole dataset then use PROC MEANS or PROC UNIVARIATE.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I add that If I just remove the median function from the code, the error disappear and the code works , which is weird cause the median function is listed as available function for fedsql https://go.documentation.sas.com/doc/en/pgmsascdc/v_038/fedsqlref/n12fyogjg75fznn1dk3worxdalfs.htm
median(precisions) as median_precision
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Median() is "row function" i.e. it works on multiple variables, e.g. median(a.x,a.y,a.z) and returns medin of x, y, and z.
Mean() is "row function" too.
Your code works wit Mean() because Mean() is alias for AVG() and AVG() as "column function".
Two different pages in doc:
AVG: https://go.documentation.sas.com/doc/en/pgmsascdc/v_038/fedsqlref/p0914cxk53dlurn1lmnfevdiekm2.htm
MEAN: https://go.documentation.sas.com/doc/en/pgmsascdc/v_038/fedsqlref/p1gtv0q63ascpcn139jw8tpnvbjw.htm
Bart
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug
"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings
SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
There is no aggregate MEDIAN() function in FEDSQL.
Since the MEDIAN of a single value is just that value using it that way in your query makes no sense. If you replace it with its equivalent
precisions as median_precision
It is clearer why FEDSQL is complaining. Unlke PROC SQL in FEDSQL SAS will not automatically remerge aggregate results back onto all of the individual rows.
If you want to calculate the median over the whole dataset then use PROC MEANS or PROC UNIVARIATE.