re Updating table

[twildone]

Hi....I am trying to update AccessCampus on the Acad table with AccessCampus from table BAAECE. The records in table BAACE are unique whereas records in Acad could be many. When I run proc sql to create Acad1, it takes a long time to run. I am looking for a better and faster way to join these tables. I was going to try and use Update statement but from what I could find out is that the Update Statement will work if the join was one to one. Any suggestions.

proc sql noprint;
    create table BAAECE as 
        select distinct 
            SROffer.Department, 
            SROffer.Course,  
            SROffer.Section,
            SROffer.Programs, 
            SROffer.AccessCampus,  
            SROffer.TermCalendarID, 
            SROffer.SROfferID
        from work.SROffer;
quit;

proc sql noprint;
	create table Acad1 as 
		select distinct
            Acad.ID,
            coalescec(Acad.Department,BAAECE.Department) as Department,
 			coalescec(BAAECE.AccessCampus,Acad.AccessCampus) as AccessCampus,
          	coalescec(Acad.Programs,BAAECE.Programs) as Programs, 
          	coalescec(Acad.Section,BAAECE.Section) as Section, 
          	coalesce(Acad.SROfferID,BAAECE.SROfferID) as SROfferID,  
          	coalesce(Acad.TermCalendarID,BAAECE.TermCalendarID) as TermCalendarID
    from work.Acad left join work.BAAECE on (Acad.SROfferID = BAAECE.SROfferID and Acad.Department = BAAECE.Department and Acad.Programs = BAAECE.Programs
            and Acad.TermCalendarID = BAAECE.TermCalendarID and Acad.Section = BAAECE.Section);
quit;

[ballardw · Accepted solution]

How many records involved in each table?

Did you consider a data step MERGE?

The Proc SQL update clause does not have any problem with one-to-one but doesn't like attempting to update multiple variables, at least the last time I tried.

These two coalescec requests a bit questionable to include in my opinion as you are using those two values in your JOIN ON criteria. If the join is correct these are just wasting clock cycles, if the join is not correct then I am not sure what benefit they would have.

            coalescec(Acad.Department,BAAECE.Department) as Department,
 		
          	coalescec(Acad.Programs,BAAECE.Programs) as Programs, 

[twildone]

Hi BallardW.....I will remove the two coalescec and run it again and see if that helps. I always thought that I should use coalescec on the variables that the tables are joined on as well. This is good to know.