BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
2 REPLIES 2
Doyleuk
Calcite | Level 5
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;
LOK
Calcite | Level 5 LOK
Calcite | Level 5
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 648 views
  • 0 likes
  • 3 in conversation