topic Re: SAS SQL problem in SAS Programming

SAS SQL problem

Perseusb — Thu, 22 Feb 2018 12:31:41 GMT

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

Re: SAS SQL problem

Perseusb — Thu, 22 Feb 2018 12:34:40 GMT

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

Re: SAS SQL problem

BrunoMueller — Thu, 22 Feb 2018 13:32:28 GMT

Your code looks ok.

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

Re: SAS SQL problem

Perseusb — Thu, 22 Feb 2018 14:14:58 GMT

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

Re: SAS SQL problem

BrunoMueller — Thu, 22 Feb 2018 19:31:43 GMT

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-data-AKA-generate/ta-p/258712

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
;