Help using Base SAS procedures

SAS File System Full Error

Reply
Regular Contributor
Regular Contributor
Posts: 238

SAS File System Full Error


What does SAS file system full error mean?

SAS Super FREQ
Posts: 8,868

Re: SAS File System Full Error


Hi:

  What is the full text of the error message? Generally, if you enter the text into the search box at support.sas.com, you will find Tech Support notes such as this:

ERROR: Write to libref.sasdata.DATA failed. File is full and may be damaged. (http://support.sas.com/kb/31/401.html)

or

ERROR: Utility file write failed.  Probable disk full condition. (http://support.sas.com/kb/14/705.html)

Otherwise, the message seems fairly self-explanatory. You might want to open a track with Tech Support to explore the possibilities. They could look at all the code that was executing when you received the error message, gather information from you about your configuration and operating system and help you resolve the issue.

cynthia

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS File System Full Error

Posted in reply to Cynthia_sas

ERROR: CLI cursor extended fetch error: [IBM][CLI Driver][DB2/AIX64] SQL0968C  The file system is

       full.  SQLSTATE=57011

That is the error I get.

Contributor
Posts: 29

Re: SAS File System Full Error

Hi Tmm

This is a DB2 database error.  This means

"The disk drive or the file system used by the db2 database server is full".  Please contact your DB2 Database administrator to fix this issue or coordinate with your SAS admin to contact the DB2 admin to resolve this issue. 

Before contacting them  as a precautionary measure check your query to ensure your query is returning what you've asked for.  I mean check your query to confirm you are not doing any cartesian product which results in returning huge results.

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS File System Full Error


I think it is the query. I added 1 field that connects to a join and that is when this started happening. Prior the query ran in just 20 mins. Adding this 1 field is when it started. I have used our data dictionary to get the join and so I am not sure why it would not work. Did a test of using 1 day pull instead of an entire year and using 1 day with this new field gave me 695 rows but the query took real time 3 hours, cpu time like 2 mins or something. So I am not sure. Will have to open a tkt and have them see if they can tell me another join to use besides what is in there or some other way of getting the data in this field.

PROC Star
Posts: 7,492

Re: SAS File System Full Error

The following may help: http://support.sas.com/kb/12/402.html

Contributor
Posts: 29

Re: SAS File System Full Error

Hi ,

Could you please post the query before/after the error.  or at least the joining conditions used in your query?

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS File System Full Error


Sure. It is a long query. The items that are not commented out have been tested by my Galaxy team who enhanced the query to make it run faster. Using the entire year from what you see in the startdt and enddt takes 20 minutes to run. When I add the cert_of_cov_fl_yr and then do any of 7 possible joins about 45mins to an hour and 15mins later it will say disk system full. So I changed my query to run just for 1 day. Using any of the 7 possible joins to the table that houses cert_of_cov_fl_yr, it runs about 3 hours on any of them and returns 695 rows. There are no other possible joins listed in our data dictionary. So I am sure since it takes 3 hours to return 695 rows that account for 1 day, trying to pull the whole year it just cannot handle because 365 days worth of data multiply by 3 hours would be a total runtime of 1080 hours. So not sure what the deal is with that one particular table. And the sad thing is I cannot take that field out because the PM needs it. I can also not just remove it for now and run the one that does run for 20minutes then do a group by the clm_aud_nbr and mbr_sys_id and do a left join to the contract plan coverage table because I am connecting via a DB2 and we cannot take data we extract onto our local machine and link it back to the server in SAS. I can do it in Access and have attempted but it is too large for Access to handle and Access will bomb.

options  compress=yes reuse=yes;

%let pass='Year2012';

%let startdt='06/01/2011';

%let enddt='05/31/2011';

%let hgh=('80428','80429','80430','83003','84305','86277','J2940','J2941','S9558','Q0515');

proc sql;

connect to db2(database=dsn user=username password=&pass);

create table hgh.claims as

select * from connection to db2

(SELECT

  1. a.clm_aud_nbr,

mbr_sys_id,

fst_srvc_dt,

sbscr_nbr,

policy_nbr,

mbr_lst_nm,

mbr_fst_nm,

adj_hccc_cd,

prov_tin,

proc_cd,

diag_1_cd,

diag_2_cd,

diag_3_cd,

diag_4_cd,

mpin,

full_nm,

prdct_cd,

company_cd,

grp_cust_nbr,

er_grp_id,

ben_pln_nbr,

cert_of_cov_fl_yr,

er_grp_id,

grp_cust_nbr,

ben_pln_nbr,

ben_strct_1_cd,

ben_strct_2_cd,

ben_cov_typ_cd,

mbr_cov_mo_row_eff_dt,

mbr_cov_mo_row_end_dt,

finc_arng_cd,

finc_arng_desc,

ama_pl_of_srvc_cd,

ama_pl_of_srvc_desc,

orig_srvc_cd,

member_mkt,

provider_mkt,

bil_typ_cd,

alloc_rvnu_cd,

fm_typ_cd,

fm_typ_desc,

sum(allw_amt) as alloc_allw_amt,

sum(elig_amt) as alloc_elig_amt,

sum(net_pd_amt) as alloc_net_pd_amt,

sum(sbmt_chrg_amt) as alloc_sbmt_chrg_amt

FROM

    (

    SELECT

        DISTINCT

        unet.clm_aud_nbr

    FROM

        galaxy.unet_claim_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

    WHERE

        co.finc_arng_cd IN ('F', 'A') AND

        mem.cob_ind = ('N') AND

        blc.bus_line_desc = 'COMMERCIAL' AND

        unet.fst_srvc_dt BETWEEN &startdt and &enddt AND

        unet.chrg_sts_cd IN ('P') AND

        unet.proc_cd IN &hgh and

            unet.allw_amt >0

        order by 1

    )

    b

INNER JOIN

    (SELECT

         unet.clm_aud_nbr,

         unet.mbr_sys_id,

         unet.sbscr_nbr,

         head.co_cd as company_cd,

         head.mbr_lst_nm,

         head.mbr_fst_nm,

         mem.grp_cust_nbr,

         head.ben_strct_1_cd,

         head.ben_strct_2_cd,

         head.ben_cov_typ_cd,   

         unet.cust_seg_nbr AS policy_nbr,

         unet.fst_srvc_dt,

         unet.adj_hccc_cd,

         unet.prov_tin,

         unet.proc_cd,

         unet.diag_1_cd,

         unet.diag_2_cd,

         unet.diag_3_cd,

         unet.diag_4_cd,

         prov.mpin,

         prov.full_nm,

         co.finc_arng_cd,

         co.finc_arng_desc,

         head.prdct_cd,

         plc.ama_pl_of_srvc_cd,

         plc.ama_pl_of_srvc_desc,

         market.maj_mkt_nm AS member_mkt,

         market2.maj_mkt_nm AS provider_mkt,

         unet.orig_srvc_cd,

         bill.bil_typ_cd,

         mem.mbr_cov_mo_row_eff_dt,

         mem.mbr_cov_mo_row_end_dt,

         unet.rvnu_cd as alloc_rvnu_cd,

         mem2.er_grp_id,

         mem3.ben_pln_nbr,

         cert.cert_of_cov_fl_yr,

         head.fm_typ_cd,

         frm.fm_typ_desc,

         allw_amt,

         elig_amt,

         net_pd_amt,

         sbmt_chrg_amt

     FROM

         galaxy.unet_claim_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.product prod

        ON head.prdct_cd = prod.prdct_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

            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.member mem2

            on head.mbr_sys_id = mem2.mbr_sys_id

            inner join galaxy.member_coverage mem3

            on mem2.mbr_sys_id = mem3.mbr_sys_id

            /*inner join galaxy.customer_segment cust

            on unet.cust_seg_sys_id = cust.cust_seg_sys_id

            and unet.cust_seg_row_eff_dt = cust.cust_seg_row_eff_dt

            inner join galaxy.parent_customer parent

            on cust.par_cust_nbr = parent.par_cust_nbr

            and cust.src_sys_cd = parent.src_sys_cd

        INNER JOIN galaxy.contract_plan_coverage cert

            on parent.par_cust_sys_id = cert.par_cust_sys_id*/ /*first poss if using cust to parent*/

          inner join galaxy.contract_plan_coverage cert

            on prod.prdct_cd = cert.prdct_cd      

        and prod.src_sys_cd = cert.src_sys_cd

            inner join galaxy.form_type_code frm

            on head.fm_typ_cd = frm.fm_typ_cd

) a

ON a.clm_aud_nbr = b.clm_aud_nbr

group by

  1. a.clm_aud_nbr,

mbr_sys_id,

sbscr_nbr,

policy_nbr,

mbr_lst_nm,

mbr_fst_nm,

fst_srvc_dt,

adj_hccc_cd,

prov_tin,

proc_cd,

diag_1_cd,

diag_2_cd,

diag_3_cd,

diag_4_cd,

mpin,

full_nm,

prdct_cd,

company_cd,

ben_strct_1_cd,

ben_strct_2_cd,

ben_cov_typ_cd,

mbr_cov_mo_row_eff_dt,

mbr_cov_mo_row_end_dt,

finc_arng_cd,

finc_arng_desc,

ama_pl_of_srvc_cd,

ama_pl_of_srvc_desc,

orig_srvc_cd,

member_mkt,

provider_mkt,

bil_typ_cd,

alloc_rvnu_cd,

er_grp_id,

grp_cust_nbr,

ben_pln_nbr,

cert_of_cov_fl_yr,

fm_typ_cd,

fm_typ_desc)

order by clm_aud_nbr;

disconnect from db2;

quit;

Ask a Question
Discussion stats
  • 7 replies
  • 2204 views
  • 0 likes
  • 4 in conversation