topic Re: SAS Issue with null values in SAS Procedures

SAS Issue with null values

tmm — Tue, 21 Aug 2012 13:27:47 GMT

I am trying to query a column that has multiple nulls but the proc sql does not work when I put in is null. It is a text field. Is there something different I should put in? This is part of my code. I tried unet.proc_cd is null and that returned no rows which I know is not true. So now I have tried it as a string.

mem.cob_ind in ('N')

and

unet.alloc_rvnu_cd in &rev /***chngd took out the cpt_cds original prior auth code***/

and

unet.proc_cd in (' ') /****added*****/

and

unet.prov_prtcp_sts_cd in ('N') /***added***/

Re: SAS Issue with null values

Tom — Tue, 21 Aug 2012 15:37:48 GMT

Did you have trouble pasting your code or did the forum do some strange formatting? There appear to be extraneous characters "a. " after every AND in your code fragment.

What do you mean by multiple null values? It looks like you are talking about a character variable. In SAS a character variable is fixed length and padded with blanks. There is no such thing as a null value for character variables.

So your code fragment

unet.proc_cd in (' ')

Should just be coded as

unet.proc_cd = ' '

Now if the libref UNET is pointing to database (Oracle, SQLnet, etc.) then SAS might be trying to push the conditional into the database. In that case the rules for character variables might be different that what you would see if you waited for SAS to pull the data into SAS table.

Re: SAS Issue with null values

tmm — Tue, 21 Aug 2012 15:45:55 GMT

Yeah. Our SAS is set up so it will not let you copy from the SAS 9.2 screen and paste into here. So I have to paste it to something else like word or I normally use my outlook because I don't have word open and it always puts letters in front. Not sure why and I forget sometimes to remove those. I did try:

unet.proc_cd = ' '

However it still returned some proc_cd's. I looked at the table and it seems this unet.proc_cd field is text and 7 in length. So I am now trying ' '

counting 7 out and see if that will return all nulls. I cannot say is null because that was my very first thought. is null did not work. I thought text, character, numeric could all us is null. Oh you know maybe it has to be isnull all together. did not think of that. will try that too and let you know.

Re: SAS Issue with null values

Tom — Tue, 21 Aug 2012 16:28:07 GMT

You might want to look at the rest of your query to make sure that your logic is correct.

Re: SAS Issue with null values

tmm — Tue, 21 Aug 2012 16:49:16 GMT

OK. It is still not working no matter what i do. I still get some returns where the proc_cd is not null. It will return all the claim lines but then some proc_cd have a value. I think I need to put it in the main query just not sure where. If I put the unet.proc_cd is null or = '' within the where statement it will still return claims and claim lines that have proc_cds cuz it is within the subquery. If i put it in the first query I think it will work but not sure where to put it.

proc sql;

create table auth.claims as

select * from connection to db2

(select

a.clm_aud_nbr,

mbr_sys_id,

fst_srvc_dt,

adj_hccc_cd,

prov_tin,

proc_cd,

compat_cd,

alloc_rvnu_cd,

mpin,

full_nm,

finc_arng_cd,

finc_arng_desc,

AMA_PL_OF_SRVC_CD,

ama_pl_of_srvc_desc,

member_mkt,

Provider_mkt,

Prov_Status, /*added just in to QC*/

FACL_TYP_TXT,

sum(epd_calc_allw_amt) as epd_calc_allw_amt,

sum(alloc_allw_amt) as alloc_allw_amt,

sum(alloc_sbmt_chrg_amt) as alloc_sbmt_chrg_amt,

sum(alloc_net_pd_amt) as alloc_net_pd_amt

from

(SELECT

unet.clm_aud_nbr,

unet.mbr_sys_id,

unet.fst_srvc_dt,

unet.adj_hccc_cd,

unet.prov_tin,

unet.proc_cd,

unet.compat_cd,

unet.alloc_rvnu_cd,

prov.mpin,

prov.full_nm,

co.finc_arng_cd,

co.finc_arng_desc,

plc.AMA_PL_OF_SRVC_CD,

plc.ama_pl_of_srvc_desc,

market.mkt_nm as member_mkt,

MARKET2.MKT_NM as Provider_mkt,

unet.prov_prtcp_sts_cd as Prov_Status, /***added just in to QC***/

BILL.FACL_TYP_TXT,

epd_calc_allw_amt,

alloc_allw_amt,

alloc_sbmt_chrg_amt,

alloc_net_pd_amt

from

galaxy.unet_ub92_revenue_code_statistical_service unet

inner join galaxy.unet_claim_header head

on unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id

and

unet.mbr_sys_id = head.mbr_sys_id

inner join galaxy.provider prov

on unet.srvc_prov_row_eff_dt = prov.prov_row_eff_dt

and

unet.srvc_prov_sys_id = prov.prov_sys_id

inner join galaxy.company_code co

on head.co_cd = co.co_cd

inner join galaxy.place_of_service_code plc

on unet.pl_of_srvc_sys_id = plc.pl_of_srvc_sys_id

inner join galaxy.unet_market market

on head.geo_area_nbr = market.mkt_nbr

and

head.geo_area_nbr_mkt_row_eff_dt = market.mkt_row_eff_dt

inner join galaxy.UNET_FACILITY_UB92 ub92

on head.mbr_sys_id=ub92.mbr_sys_id

and

head.UNET_CLM_HEAD_SYS_ID =ub92.UNET_CLM_HEAD_SYS_ID

INNER JOIN Galaxy.COSMOS_BILL_TYPE_CODE BILL

ON ub92.BIL_TYP_CD = BILL.BIL_TYP_CD

INNER JOIN galaxy.UNET_MARKET MARKET2

on unet.PROV_CONTR_MKT_NBR = MARKET2.MKT_NBR

and

unet.PROV_CONTR_MKT_ROW_EFF_DT = MARKET2.MKT_ROW_EFF_DT )a

inner join

(select distinct unet.clm_aud_nbr from

galaxy.unet_ub92_revenue_code_statistical_service unet

inner join galaxy.unet_claim_header head

on unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id

and

unet.mbr_sys_id = head.mbr_sys_id

inner join galaxy.company_code co

on head.co_cd = co.co_cd

inner join galaxy.product prod

on head.prdct_cd = prod.prdct_cd

inner join galaxy.business_line_code blc

on prod.bus_line_cd = blc.bus_line_cd

inner join galaxy.member_coverage_month mem

on unet.mbr_sys_id = mem.mbr_sys_id

and unet.fst_srvc_dt between mem.MBR_COV_MO_ROW_eff_DT

and mem.MBR_COV_MO_ROW_END_DT

inner join galaxy.place_of_service_code plc

on unet.pl_of_srvc_sys_id = plc.pl_of_srvc_sys_id

where

unet.alloc_rvnu_cd in &rev

and

co.finc_arng_cd in ('F','A')

and

mem.cob_ind = ('N')

and

plc.AMA_PL_OF_SRVC_CD='22'

and

blc.bus_line_desc = 'COMMERCIAL'

AND

unet.fst_srvc_dt between &startdt and &enddt

and

unet.prov_prtcp_sts_cd in ('N') /***added***/

and

unet.chrg_sts_cd in ('P')

and

unet.alloc_rvnu_cd not in ('0450','0451','0452','0453','0454','0455','0456','0457','0458','0459'))b

on a.clm_aud_nbr=b.clm_aud_nbr

group by

a.clm_aud_nbr,

mbr_sys_id,

fst_srvc_dt,

adj_hccc_cd,

prov_tin,

proc_cd,

compat_cd,

alloc_rvnu_cd,

mpin,

full_nm,

finc_arng_cd,

finc_arng_desc,

ama_pl_of_srvc_cd,

ama_pl_of_srvc_desc,

member_mkt,

Provider_mkt,

Prov_Status, /***added just in to QC***/

FACL_TYP_TXT)

order by clm_aud_nbr;

disconnect from db2;

quit;

Re: SAS Issue with null values

Doc_Duke — Tue, 21 Aug 2012 17:27:06 GMT

This appears to be a pass-thru query to db2. look at the documentation for the SAS/Access for DB2 to see how to handle nulls in character data.

Re: SAS Issue with null values

tmm — Tue, 21 Aug 2012 17:30:58 GMT

That was one of the first things I did. I am just not sure where to put in unet.proc_cd is null within the query so that it looks for that before it does the subquery. Doing it within the sub or passthru does read the code but still puts in those where there is a proc_cd

Re: SAS Issue with null values

Hetty — Tue, 21 Aug 2012 19:16:15 GMT

Wow, that's a complicated query and it's quite possible one of your joins or conditions is kicking out your null values. Are there any messages in your log saying something like 'proc_cd already exists'? That said, there is a difference between a character field filled with blanks and one that is null. You might want to run this query again and output your proc_cd in hexadecimal format. You could also try putting your null criterion right in the first join like this:

galaxy.unet_ub92_revenue_code_statistical_service unet

inner join galaxy.unet_claim_header head

on unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id

and

unet.mbr_sys_id = head.mbr_sys_id

AND unet.proc_cd is null

inner join etc.

Re: SAS Issue with null values

tmm — Tue, 21 Aug 2012 19:56:39 GMT

I will try that.