Help using Base SAS procedures

SAS Issue with null values

Reply
Regular Contributor
Regular Contributor
Posts: 238

SAS Issue with null values

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.

  1. mem.cob_ind in ('N')

and

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

and

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

and

  1. unet.prov_prtcp_sts_cd in ('N')    /***added***/
Super User
Super User
Posts: 6,502

Re: SAS Issue with null values

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.

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Issue with null values

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.

Super User
Super User
Posts: 6,502

Re: SAS Issue with null values

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

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Issue with null values

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;

Trusted Advisor
Posts: 2,113

Re: SAS Issue with null values

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.

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Issue with null values

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

Occasional Contributor
Posts: 8

Re: SAS Issue with null values

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.

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Issue with null values

I will try that.

Ask a Question
Discussion stats
  • 8 replies
  • 379 views
  • 0 likes
  • 4 in conversation