BookmarkSubscribeRSS Feed
smilbuta
Fluorite | Level 6
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
1 REPLY 1
DBailey
Lapis Lazuli | Level 10
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1512 views
  • 0 likes
  • 2 in conversation