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.
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;
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;
Thanks RW9 for the quick reply, the array worked perfectly, much appreciated!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.