topic Re: Missing values understanding in SAS Programming

Missing values understanding

Babloo — Tue, 21 Aug 2018 13:03:46 GMT

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

1997938

PARTY_SK

-1

PARTY_ID

100

1000

10000

100000

1000000

Re: Missing values understanding

Kurt_Bremser — Tue, 21 Aug 2018 12:47:27 GMT

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.

Re: Missing values understanding

Kurt_Bremser — Tue, 21 Aug 2018 13:14:18 GMT

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