topic Re: Merging Issue in SAS Programming

Merging Issue

GunnerEP — Thu, 16 Mar 2017 04:51:08 GMT

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!

Re: Merging Issue

JohnChen_TW — Thu, 16 Mar 2017 05:14:52 GMT

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;".

Re: Merging Issue

GPNaveen — Thu, 16 Mar 2017 05:37:52 GMT

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,

Re: Merging Issue

Reeza — Thu, 16 Mar 2017 05:40:03 GMT

Look at the UPDATE statement instead of a merge.

Re: Merging Issue

Yavuz — Thu, 16 Mar 2017 06:09:39 GMT

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;

Re: Merging Issue

GunnerEP — Thu, 16 Mar 2017 07:26:10 GMT

Thanks Naveen. Code was fine.. i had trouble with the input dataset only. Got it fixed 🙂