topic How to resolve 'unresolved reference to table/correlation name inf/'? in New SAS User

How to resolve 'unresolved reference to table/correlation name inf/'?

anucharbe — Mon, 16 Nov 2020 21:24:36 GMT

I am working on a proc sql code; it is giving me the error below:

1. ERROR: Summary functions nested in this way are not supported.

proc sql;
create table A.TAT as select Month, MailerTurnAroundTime, 
Sum(count(distinct(Patient_id)))as Counts from 
(select case when mailerdays < 4 then "Less than 4 days"
when mailerdays >= 4 and mailerdays <= 7 then  "4 to 7 days"
when mailerdays > 7 then  "Greater than 7 days"
end 
as MailerTurnAroundTime
, *
from A.Mailertat)
group by Month, MailerTurnAroundTime;
quit;

I am sure the error is something simple that I haven't caught yet. Can anyone please help?

Re: How to resolve 'unresolved reference to table/correlation name inf/'?

PaigeMiller — Mon, 16 Nov 2020 21:30:36 GMT

Show us the actual LOG for this PROC SQL stepped, unedited, with nothing chopped out. Preserve the formatting of the LOG by copying it as text and pasting it into the window that appears when you click on the </>. Please help us, this preserves the formatting, DO NOT SKIP THIS STEP.

Re: How to resolve 'unresolved reference to table/correlation name inf/'?

Tom — Mon, 16 Nov 2020 21:48:48 GMT

Since you are selecting from a sub-query and not an actual dataset you need to give the sub-query and alias name, even if the sub-query is the only thing you are selecting from. So XXX in this snippet is the alias.

from (select ....) xxx

Re: How to resolve 'unresolved reference to table/correlation name inf/'?

FreelanceReinh — Mon, 16 Nov 2020 22:37:09 GMT

Hi @anucharbe,

The "nested summary functions" are Sum(...) and count(...). Within each combination of Month and MailerTurnAroundTime values you want to count the distinct Patient_id values, which is fine. But then how does the SUM function come into play? I suspect that you mean the sum of the counts across Month-MailerTurnAroundTime combinations, not within these groups, i.e., the GROUP BY clause should not apply to the SUM function. This would require another (nested) inline view, for example:

select Month, MailerTurnAroundTime, sum(npat) as Counts
from (select Month, MailerTurnAroundTime, count(distinct Patient_id) as npat
      from (select case when ...)
      group by Month, MailerTurnAroundTime);

Re: How to resolve 'unresolved reference to table/correlation name inf/'?

anucharbe — Mon, 16 Nov 2020 23:27:35 GMT

Thank you, that worked with my code. 🙂