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 |
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.
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.
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
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!
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.
Ready to level-up your skills? Choose your own adventure.