SAS SQL problem

[Perseusb]

I found a very strange error in SAS EG, when I run below sas code:

PROC SQL;
Create table tmp as
Select a.id, a.code, b.type
From base as a
Left join staff as b on a.code=b.code

The result tmp dataset will have id value and NULL code and NULL type

The data type and value of the code value in table base and staff are correct, but I cannot understand why the value code in table base will become NULL in table tmp. Please help. Thanks

Peter

[Perseusb]

E.g.

Table base
Id Code
—————
1 A
2 A
3 B

Table staff
Code Type
——————
A Ok
B Fail

Table tmp
Id Code Type
————————
1
2
3

[BrunoMueller]

Your code looks ok.

If your data is like you say there should be no problem.

[Perseusb]

I'm expecting to have below result:

Table tmp
Id Code Type
————————
1   A   OK
2   A   OK
3   B   Fail

or at least below result if the code cannot be mapped:

Table tmp
Id Code Type
————————
1   A  
2   A  
3   B  

But I just cannot understand why I can get below result (with Code value became all NULL)

Table tmp
Id Code Type
————————
1  
2  
3  

[BrunoMueller]

Provide your data as a DATA Step.

See this post on how to do it:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Since your data is simple, you can write the DATA Step quite easy yourself, see example below

data tmp;
  input
    Id
    Code $
  ;
cards;
1 A 
2 A 
3 B
;