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