Hi,
I have a problem with left join inside the SAS merge:
I have two table(I can't give you the real tables or the log files, becouse the policy of the enterprise where I'm working is very strictly 😞
table A:
id source _a _b
1 a re 123
2 b ba 53
3 a ag 679
3 b 45 78
table B
id number
1 13
2 14
3 2
If I will make a left join with this SAS code:
data C;
merge a(in=a)
b(in=b);
by id;
if a;
if source = 'a' then number = . ;
run;
the data set has both of row 3 with the missing value, instead of just one(with the source = a);
is there a logical mystake in the sas code(merge)? i
You should be aware of :
That different problems may arises when MERGE, IF-THEN statement used within one data step.
Kindly check:
Be Careful When You Merge SAS Datasets!
So use two data step instead of one, as one work around for your case.
mohamed zaki
SAS and MERGE are working properly. What result do you seek?
hi data _null_,
I would like to have ths result :
TABLE C:
id source _a _b number
1 a re 123 13
2 b ba 53 14
3 a ag 679 .
3 b 45 78 2
What rules you use to get this outcome?
i think his last post contain typo
it should be
TABLE C:
id source _a _b number
1 a re 123 .
2 b ba 53 14
3 a ag 679 .
3 b 45 78 2
as based on the original post.
Hi ,
sorry for my delay in my answer....
I yes the result is what that zaki said...
I used the code of hai.kuo and it works.
thanks!
if @mohammed.zaki read your mind correctly, then a little tweak in your code is needed:
data C;
merge a(in=a)
b(in=b rename=number=num);
by id;
if a;
if source = 'a' then
number = .;
else number=num;
drop num;
run;
You should be aware of :
That different problems may arises when MERGE, IF-THEN statement used within one data step.
Kindly check:
Be Careful When You Merge SAS Datasets!
So use two data step instead of one, as one work around for your case.
mohamed zaki
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.