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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.