How do I merge two datasets

[Sandeep77]

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.

[Tom · Accepted solution]

You are not JOINing the dataset.  Union will merely STACK the results of the two separate queries.  Because they are separate queries the second one knows nothing about the dataset mentioned in the first query.

What is the purpose of the WHERE clause?  Why not just eliminate it?

Did you mean to just keep the records from the second dataset that have ids that appear in the first dataset?  Then the WHERE clause of the second query should look like:

select *
from work.current_address
where Current_Address.debt_code in (select Combined.debt_code from work.Combined)

[Tom]

Much easier to do with SAS code than SQL code.

data Combined_with_cur_add ;
  set Combined Current_Address;
run;