topic Re: merge data in SAS Procedures

merge data

deleted_user — Fri, 08 May 2009 08:13:28 GMT

i have 2 files, I merge it by key id

file 1
v1 id
v2 name
v3 address1

file 2
v1 id
v2 sales amt
v3 address2
v4 flag (Y/N)

I want to merge above 2 files by id and if v4 flag in file 2 ="Y" I will use address2 if ="N" will use address1 in file 1 , output I want

id name sale amt address(1 or 2)

thanks in advance

Re: merge data

Doyleuk — Fri, 08 May 2009 11:40:32 GMT

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;

Re: merge data

LOK — Thu, 21 May 2009 10:04:49 GMT

data file1;
input ID name$ add1$ ;
datalines;
1 nikki mumbai
2 pavan HYD
3 prani chennai
;
run;

data file2;
input ID name$ add2$ Sales$ Flag$;
datalines;
1 nikki mumbai 1000 y
2 pavan kochi 2000 n
3 prani chennai 1000 y
;
run;

proc sql;
select one.ID, one.name, two.sales, two.flag,
case when two.flag = 'y' then add2
when two.flag = 'n' then add1
else 'No Address'
end as address
from file1 as one,
file2 as two
where one.ID=two.ID;
quit;