PROC SQL: help with code syntax problem

smilbuta — Mon, 28 Feb 2011 15:29:34 GMT

Hello community,

I am not sure what the problem here is.
I am trying to perform an update on a dataset based off of another data set.

ERROR 1 pre change.
19 Proc SQL;
20 Update RO.CH_HIST_MASTER as t1
21 set ACAD_ORG = (Select NewOrg from RO.CAH_CONV as t2
22 where t2.Subject = t1.Subject
23 and t2.Catalog = CATALOG_NBR
24 and t2.Group = t1.ACAD_GROUP)
25 where t1.Subject = t2.Subject
26 and t1.CATALOG_NBR = t2.Catalog
27 and t1.ACAD_GROUP = t2.Group
28 and t1.STRM = "1390";
ERROR: Unresolved reference to table/correlation name t2.
ERROR: Unresolved reference to table/correlation name t2.
ERROR: Unresolved reference to table/correlation name t2.

When i change the t2 reference in the second WHERE clause it returns another error. Both errors shown below, any assistance pointing me in the right direction would be help full.

ERROR 2 Post change
19 Proc SQL;
20 Update RO.CH_HIST_MASTER as t1
21 set ACAD_ORG = (Select NewOrg from RO.CAH_CONV as t2
22 where t2.Subject = t1.Subject
23 and t2.Catalog = CATALOG_NBR
24 and t2.Group = t1.ACAD_GROUP)
25 where t1.Subject = CAH_CONV.Subject
26 and t1.CATALOG_NBR = CAH_CONV.Catalog
27 and t1.ACAD_GROUP = CAH_CONV.Group
28 and t1.STRM = "1390";
ERROR: Unresolved reference to table/correlation name CAH_CONV.
ERROR: Unresolved reference to table/correlation name CAH_CONV.
ERROR: Unresolved reference to table/correlation name CAH_CONV. Message was edited by: smilbuta

Re: PROC SQL: help with code syntax problem

DBailey — Mon, 28 Feb 2011 16:39:49 GMT

How about this?

Proc SQL:
update RO.CH_HIST_MASTER as t1
set ACAD_ORG = (Select NewOrg from RO_CAH_CONV where subject=t1.subject and catalog=t1.catalog_nbr
and t2.group=t1.acad_group)
where exists (select * from ro_cah_conv subject=t1.subject and catalog=t1.catalog_nbr
and t2.group=t1.acad_group and strm='1390');
quit;

topic PROC SQL: help with code syntax problem in SAS Procedures

PROC SQL: help with code syntax problem

Re: PROC SQL: help with code syntax problem