DATA Step, Macro, Functions and more

Merging Issue

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Merging Issue

Have 2 datasets Old & New, Old have some location id with its details field, the new list have location id only details field will be blank.

 

Dataset New :-

Location id    Area    Phone
1234
2345
1233
5543

Dataset Old :-

Location id   Area     Phone
1234            NYC     55555
2345             ABC     77777

 

Output Dataset Expected:

Location id Area Phone
1234 NYC 55555
2345 ABC 77777
1233
5543

 

Code written:-

proc sort data=New nodupkey;by merchant_market_hierarchy_id;

proc sort data=Old nodupkey;by merchant_market_hierarchy_id;

 

data New;
merge New(in=ln) Old(in=lo);
by location_id;
if ln;

 

My output is not as expected. 

What am i doing wrong here? would require some help on this please!


Accepted Solutions
Solution
‎03-16-2017 03:25 AM
Occasional Contributor
Posts: 6

Re: Merging Issue

Hello,

 

I see the code to be fine, may be that while the New dataset is being read into a dataset the missing fields are not considered in the expected way.I would suggest to use MISSOVER option in the infile and then try the code .It must do your job.

 

Example:

 

Data New;
infile datalines missover;
input Location_id Area$ Phone;
datalines;
1234
2345
1233
5543
;
run;

 

Data Old;
Input Location_id Area$ Phone;
Datalines;
1234 NYC 55555
2345 ABC 77777
;
run;

 

proc sort data=New nodupkey;
by Location_id;
Run;

proc sort data=Old nodupkey;
by location_id;
Run;

data New1;
merge New(in=a) OLD(in=b);
by location_id;
if a;
run;

 

 

Thanks,

GP

View solution in original post


All Replies
Contributor
Posts: 56

Re: Merging Issue

[ Edited ]

Because location 1234 and 2345 is blank in New. The merged data will be the data from Old covered by New due to "if in".

Maybe you can try to merge New with Old without "if in;".

Solution
‎03-16-2017 03:25 AM
Occasional Contributor
Posts: 6

Re: Merging Issue

Hello,

 

I see the code to be fine, may be that while the New dataset is being read into a dataset the missing fields are not considered in the expected way.I would suggest to use MISSOVER option in the infile and then try the code .It must do your job.

 

Example:

 

Data New;
infile datalines missover;
input Location_id Area$ Phone;
datalines;
1234
2345
1233
5543
;
run;

 

Data Old;
Input Location_id Area$ Phone;
Datalines;
1234 NYC 55555
2345 ABC 77777
;
run;

 

proc sort data=New nodupkey;
by Location_id;
Run;

proc sort data=Old nodupkey;
by location_id;
Run;

data New1;
merge New(in=a) OLD(in=b);
by location_id;
if a;
run;

 

 

Thanks,

GP

Contributor
Posts: 32

Re: Merging Issue

Thanks Naveen. Code was fine.. i had trouble with the input dataset only. Got it fixed Smiley Happy
Super User
Posts: 17,963

Re: Merging Issue

Look at the UPDATE statement instead of a merge. 

Contributor
Posts: 43

Re: Merging Issue

you've especially  asked for merge statement but you can solve it by sql statement;


DATA NEW;
INPUT Location_id;
DATALINES;
1234
2345
1233
5543
;
RUN;


DATA OLD;
INPUT Location_id Area $ Phone $;
DATALINES;
1234 NYC 55555
2345 ABC 77777
;
run;

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_NEW AS
SELECT t1.Location_id,
t2.Area,
t2.Phone
FROM WORK.NEW t1
LEFT JOIN WORK.OLD t2 ON (t1.Location_id = t2.Location_id);
QUIT;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 231 views
  • 0 likes
  • 5 in conversation