Give this a try mate, You wil be able to amend where needed
data file1;
infile datalines delimiter=',' missover;
length ID name add1 $20. ;
input ID name add1 ;
datalines;
1,Allied,1 Test Ave
2,Apex,2 Test Cres
3,Bluestone,
4,Capquest,
5,CARS,5 Test View
;
run;
data file2;
infile datalines delimiter=',' missover;
length ID name add2 sale $20. flag $2.;
input ID name add2 Sale Flag;
datalines;
1,Allied,1 Test Ave,1234,Y
2,Apex,2 Test Cres,4785,Y
3,Bluestone,3 Test Close,88897,N
4,Capquest, ,3597,Y
5,CARS, ,17112,Y
;
run;
proc sql;
create table new_table as
select a.ID, a.name, b.ID, b.name, b.sale, b.flag, case when a.add1 ne ' ' then add1
when a.add1 = ' ' and b.add2 ne ' ' then b.add2
when a.add1 = ' ' and b.add2 = ' ' then 'NO ADDRESS' end as add3
from file1 as a,
file2 as b
where a.id =b.id
and b.flag = "Y"
;
quit;
... View more