DATA Step, Macro, Functions and more

SAS SQL problem

Reply
New Contributor
Posts: 3

SAS SQL problem

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
New Contributor
Posts: 3

Re: SAS SQL problem

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
SAS Super FREQ
Posts: 831

Re: SAS SQL problem

Your code looks ok.

 

 

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

 

New Contributor
Posts: 3

Re: SAS SQL problem

Posted in reply to Bruno_SAS

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  

 

SAS Super FREQ
Posts: 831

Re: SAS SQL problem

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
; 
Ask a Question
Discussion stats
  • 4 replies
  • 109 views
  • 0 likes
  • 2 in conversation