BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Babloo
Rhodochrosite | Level 12

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

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
Super User

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

 


 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1143 views
  • 0 likes
  • 2 in conversation