sasdatastep

Compare data from different data sets SAS


I have two tables table_A and table_B.

table_A

Queue_nm Cust_nm
ABCD VERI
PQRS RSTQ

table_B

Queue_nm Act_Cust_nm
PQRS TMOB

I want to compare the two datasets and if they don't match, then replace the data in table_B in table_A using SAS

data compare_test_2;
set table_A;
set table_B;

if table_A.Queue_nm = table_B.Queue_nm
and tableA.Cust_nm != table_B.Act_Cust_name
then do;
tableA.Cust_nm = table_B.Act_Cust_name
where table_A.Queue_nm = table_B.Queue_nm;
run;

I want the following output after the comparison and the data step

table_A

Queue_nm Cust_nm
ABCD VERI
PQRS TMOB

I get the following error

ERROR: DATA STEP Component Object failure.  Aborted during the COMPILATION phase.
ERROR 557-185: Variable table_A is not an object.

Solution

  • You can use SQL to update a table with values from a second table via a correlated subquery using a key variable.

    Example:

    proc sql;
      update table_a as outer
      set cust_nm = (select act_cust_nm from table_b as inner where inner.Queue_nm = outer.Queue_nm)
      where exists (select * from table_b as inner where inner.Queue_nm = outer.Queue_nm)
      ;
    

    Another way to update a table in place is the MODIFY statement.

    proc datasets nolist lib=work;
      modify table_a;
      create index Queue_nm;
    quit;
    
    data table_a;
      set table_b;
      modify table_a key=Queue_nm;
      cust_num = act_cust_num;
    run;