Hi all,
I have two datasets. One is the 'Combined' which includes application address, supply address and original address and other dataset is current address. I want to merge them and have all the four address type in one table. They both have same variables (columns) and I want to join using debt_code. I have tried Union but it doesn't work. Can you please suggest the best way to do it? I want to join all the records and then I will see how many debt_codes have missing address, name, etc and how many have values at least in one ad_type. Here is a sample data set and my code:
Data Address;
infile cards expandtabs;
input debt_code Title $ Forename $ Surname $ DOB ad_address1 $ ad_address2 $ ad_address3 $ ad_address4 $ ad_address5 $ ad_postcode $ ad_type$;
datalines ;
119409498 Miss Anne Tait . Langmuir Kirkintilloch Glasgow Lanarkshire . G662PD AP
119409498 Miss Anne Tait . Langmuir Kirkintilloch Glasgow . . G662PD DB
119566768 Miss Anne Tait . Langmuir Kirkintilloch Glasgow . . G662PD DB
119566768 Miss Anne Tait . Langmuir Kirkintilloch Glasgow Lanarkshire . G662PD AP
113585517 Mr Tuncel Ibrahim . Broke London . . . E84SJ DB
117474056 Mr Lee Roberts . Warrior Leonards-On-sea East . . TN376BP SA
118829142 Mr Barry Miller . 193 Kingsknowe Edinburgh Midlothian . EH142ED DB
;
run;
proc sql;
create table Combined_with_cur_add as
select *
from work.Combined
Union
select *
from work.current_address
where Combined.debt_code = Current_Address.debt_code;
quit;
Error log:
29 proc sql;
30 create table Combined_with_cur_add as
31 select *
32 from work.Combined
33 Union
34 select *
35 from work.current_address
36 where Combined.debt_code = Current_Address.debt_code;
ERROR: Unresolved reference to table/correlation name Combined.
WARNING: A table has been extended with null columns to perform the UNION set operation.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
37 quit;
NOTE: The SAS System stopped processing this step because of errors.