Missing values understanding

[Babloo]

I ran the following code and got too many missing values (.) for the variable 'PSI'. There is no missing values in D_PTY_PARTY.PARTY_ID, F_CLM_CLAIM_EOM.CUSTOMER_SK and D_PTY_PARTY.PARTY_SK. Could you please help me understand how would I've got missing values?

proc sql;
	create table work.W6ZVHVL0 as
		select distinct
			input(D_PTY_PARTY.PARTY_ID ,9.) as PSI length = 8 
			format = 9.
			informat = 9.
			label = 'Customer Identifier',
			F_CLM_CLAIM_EOM.AMT length = 8   
			label = 'Amount'
		from
			udmtcom.F_CLM_CLAIM_EOM, 

			udmtcom.D_PTY_PARTY
		where

			F_CLM_CLAIM_EOM.CUSTOMER_SK = D_PTY_PARTY.PARTY_SK
		
			and F_CLM_CLAIM_EOM.REPORTING_DATE_SK = 20180630
		
	;
quit;

 Example data:

CUSTOMER_SK

-1

578704

578704

1997938

PARTY_SK

-1

1

2

3

4

PARTY_ID

1

100

1000

10000

100000

1000000

[Kurt_Bremser · Accepted solution]

I see no example data against which we can test the code. Please supply such.

Because with made-up data, your SQL works:

data F_CLM_CLAIM_EOM;
input amt CUSTOMER_SK $ REPORTING_DATE_SK;
cards;
1000 XXX 20180630
;
run;

data D_PTY_PARTY;
input PARTY_ID :$9. PARTY_SK $;
cards;
123456789 XXX
;
run; 

proc sql;
	create table work.W6ZVHVL0 as
		select distinct
			input(D_PTY_PARTY.PARTY_ID ,9.) as PSI length = 8 
			format = 9.
			informat = 9.
			label = 'Customer Identifier',
			F_CLM_CLAIM_EOM.AMT length = 8   
			label = 'Amount'
		from
			F_CLM_CLAIM_EOM, 

			D_PTY_PARTY
		where

			F_CLM_CLAIM_EOM.CUSTOMER_SK = D_PTY_PARTY.PARTY_SK
		
			and F_CLM_CLAIM_EOM.REPORTING_DATE_SK = 20180630
		
	;
quit;

proc print data=W6ZVHVL0 noobs;
run;

Result:

      PSI     amt

123456789    1000

No missing value.

[Kurt_Bremser]

Please see my footnotes and the example I gave for posting data in a data step. What you added is completely useless (apart from showing that only the -1 values match).

---

@Babloo wrote:

I ran the following code and got too many missing values (.) for the variable 'PSI'. There is no missing values in D_PTY_PARTY.PARTY_ID, F_CLM_CLAIM_EOM.CUSTOMER_SK and D_PTY_PARTY.PARTY_SK. Could you please help me understand how would I've got missing values?

proc sql;
	create table work.W6ZVHVL0 as
		select distinct
			input(D_PTY_PARTY.PARTY_ID ,9.) as PSI length = 8 
			format = 9.
			informat = 9.
			label = 'Customer Identifier',
			F_CLM_CLAIM_EOM.AMT length = 8   
			label = 'Amount'
		from
			udmtcom.F_CLM_CLAIM_EOM, 

			udmtcom.D_PTY_PARTY
		where

			F_CLM_CLAIM_EOM.CUSTOMER_SK = D_PTY_PARTY.PARTY_SK
		
			and F_CLM_CLAIM_EOM.REPORTING_DATE_SK = 20180630
		
	;
quit;

 Example data:

CUSTOMER_SK

-1

578704

578704

1997938

PARTY_SK

-1

1

2

3

4

PARTY_ID

1

100

1000

10000

100000

1000000

---