How to update a dataset based on another dataset

[scott_darge]

Hi All,

I'm a fairly new SAS user and having a bit of difficulty in what I think should be something fairly straightforward...

I have a master dataset with Account IDs and 4 telephone numbers. I then have an exclusion list with Account IDs and 1 telephone number. What I'm looking to do is if the combination of both Account ID and Telephone Number in the Exclusions dataset exist in the master data set then replace it with 10 zeros. The Telephone Number could exist against another Account ID within the master dataset so it's important that it's only when it's the combination of both that it updates.

Master Set

Account_ID	Phone_Home	Phone_Business	Phone_Alt1	Phone_Alt2
1000123	1505331686	1415373252	7957777777	0
1000124	1505331687	0	7957385082	0
1000125	1505331688	1415373254	7957385083	0
1000126	1505331689	7957777777	7957385084	0
1000127	1505331690	1415373256	7957385085	7957385081
1000128	1417777777	1415373257	7957385086	0
1000129	0	0	0	7957385087
1000130	1505331693	1417777777	7957385088	0

Exclusion Set

Account_ID	Tel_Number
1000123	7957777777
1000130	1417777777
1000132	1507777777
1000137	1507777777

Any help would be greatly appreciated.

[RW9 · Accepted solution]

You could use arrays:

data master (drop=tel_number);

     merge master exclusion;

     by account_id;

     array phone{4} phone_home phone_business phone_alt1 phone_alt2;

     do I=1to 4;

          if phone{I}=tel_number then phone{I}="0000000000";

     end;

run;

Or SQL update:

proc sql;

  update MASTER M

  set PHONE_HOME="0000000000"

  where exists(select THIS.ACCOUNT_ID from WORK.EXCLUSION THIS where THIS.ACCOUNT_ID=M.ACCOUNT_ID and THIS.TEL_NUMBER=M.PHONE_HOME);

  update MASTER M

  set PHONE_BUSINESS="0000000000"

  where exists(select THIS.ACCOUNT_ID from WORK.EXCLUSION THIS where THIS.ACCOUNT_ID=M.ACCOUNT_ID and THIS.TEL_NUMBER=M.PHONE_BUSINESS);

  update MASTER M

  set PHONE_ALT1="0000000000"

  where exists(select THIS.ACCOUNT_ID from WORK.EXCLUSION THIS where THIS.ACCOUNT_ID=M.ACCOUNT_ID and THIS.TEL_NUMBER=M.PHONE_ALT1);

  update MASTER M

  set PHONE_ALT2="0000000000"

  where exists(select THIS.ACCOUNT_ID from WORK.EXCLUSION THIS where THIS.ACCOUNT_ID=M.ACCOUNT_ID and THIS.TEL_NUMBER=M.PHONE_ALT2);

quit;

[scott_darge]

Thanks RW9 for the quick reply, the array worked perfectly, much appreciated!