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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.