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!
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
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;".
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
Look at the UPDATE statement instead of a merge.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.