ERROR: Column could not be found

[jakeroth424]

I'm trying to create a column (postal_cd) and then join another table from that column, but I received this error:

ERROR: Column postal_cd could not be found in the table/view identified with the correlation name A.

proc sql;
create table TEST as
(select b.state_nm as state_c
,a.*
,a.full_zip as ZIP9
,substr(full_zip,1,5) as postal_cd
from sid.plus4_zip_summary_us a
inner join temp b
on b.zip_char_cd=a.postal_cd
);
quit;

[mklangley]

The table with the alias a is sid.plus4_zip_summary_us. That table does not have postal_cd, so a reference to a.postal_cd will give that error. However, that table does have full_zip, from which you are getting postal_cd. So you can just reference the substring of full_zip in your join:

proc sql;
create table TEST as
(select b.state_nm as state_c
,a.*
,a.full_zip as ZIP9
,substr(full_zip,1,5) as postal_cd
from sid.plus4_zip_summary_us a
inner join temp b
on b.zip_char_cd=substr(a.full_zip,1,5)
);
quit;

[ballardw]

That' pretty clear.

The data set sid.plus4_zip_summary_us  does not contain a variable name postal_cd

If you intend to create the code then you need to watch where you are placing ( ).

Maybe:

proc sql;
   create table TEST as
   select b.state_nm as state_c
   ,a.*
   ,a.full_zip as ZIP9
   ,
   from (select *, substr(full_zip,1,5) as postal_cd
         from sid.plus4_zip_summary_us) a
   inner join temp b
   on b.zip_char_cd=a.postal_cd
   ;
quit;

You were attempting to create the variable Postal_ct after the inner join. The  above creates it in a subquery so is in the alias A data.