SAS proc sql error. The following columns were not found in the contributing tables:

I am running the below proc sql code in SAS. I get the error as 'The following columns were not found in the contributing tables: I really dont understand where is the mistake.

proc sql;
 create table sum_Med
  as
  select ID, count(prop_ID) as count,
  (case 
    when (count >= 5) then 1 else 0 end) as poli
  from dist_Id
 group by ID;
quit;

Error below:

Error The following columns were not found in the contributing tables: count

Solution

In general, SQL does not allow you to refer to column aliases in the same SELECT where they are defined. That is why you are getting an error.

However, proc sql has a convenient workaround -- the calculated keyword. So you can write this as:

create table sum_Med as
  select ID, count(prop_ID) as count,
         (case when calculated count >= 5 then 1 else 0 end) as poli
  from dist_Id
  group by ID;

Of course, you can just repeat the expression as well:

create table sum_Med as
  select ID, count(prop_ID) as count,
         (case when count(prop_ID) >= 5 then 1 else 0 end) as poli
  from dist_Id
  group by ID;

This is the traditional method for solving the problem -- and is probably needed if you are using native SQL in a connection to another database.