BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
scott_darge
Calcite | Level 5


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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 917 views
  • 0 likes
  • 2 in conversation