Help using Base SAS procedures

How to update a dataset based on another dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

How to update a dataset based on another dataset


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_IDPhone_HomePhone_BusinessPhone_Alt1Phone_Alt2
10001231505331686141537325279577777770
10001241505331687079573850820
10001251505331688141537325479573850830
10001261505331689795777777779573850840
10001271505331690141537325679573850857957385081
10001281417777777141537325779573850860
10001290007957385087
10001301505331693141777777779573850880

Exclusion Set

Account_IDTel_Number
10001237957777777
10001301417777777
10001321507777777
10001371507777777

Any help would be greatly appreciated.


Accepted Solutions
Solution
‎10-31-2014 09:47 AM
Super User
Super User
Posts: 7,401

Re: How to update a dataset based on another dataset

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;

View solution in original post


All Replies
Solution
‎10-31-2014 09:47 AM
Super User
Super User
Posts: 7,401

Re: How to update a dataset based on another dataset

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;

Occasional Contributor
Posts: 6

Re: How to update a dataset based on another dataset

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 180 views
  • 0 likes
  • 2 in conversation