BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GunnerEP
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
GPNaveen
Fluorite | Level 6

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

5 REPLIES 5
JohnChen_TW
Quartz | Level 8

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

GPNaveen
Fluorite | Level 6

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

GunnerEP
Obsidian | Level 7
Thanks Naveen. Code was fine.. i had trouble with the input dataset only. Got it fixed 🙂
Reeza
Super User

Look at the UPDATE statement instead of a merge. 

Yavuz
Quartz | Level 8

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;

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!

How to Concatenate Values

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.

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
  • 5 replies
  • 1054 views
  • 0 likes
  • 5 in conversation