DATA Step, Macro, Functions and more

Error in Pass through SQL - DB2

Reply
Frequent Contributor
Posts: 135

Error in Pass through SQL - DB2

[ Edited ]
3974        ************************************************************;       
3975        ** EXTRACT CLAIM DATA FROM CLAIMS MART                    **;       
3976        ************************************************************;       
3977         PROC SQL;                                                          
3978             CONNECT TO DB2(SSID=DBR0);                                     
        EXECUTE(SET CURRENT DEGREE='ANY') BY DB2;                      
        CREATE TABLE CLAIM1.DATA AS                                    
            SELECT* FROM CONNECTION TO DB2                             
            (SELECT                                                    
               PIWD.CLM_PCM_DMNSN.JURSTCTN_DMIS_CD AS DMISCODE,        
               PIWD.CLM_PCM_DMNSN.JURSTCTN_DMIS_NAME AS DMISNAME,      
               PIWD.CLNDR_TIME_DMNSN.CLNDR_DATE AS SERVICEDATE,        
               PIWD.CLM_HDR_DMNSN.CLM_BASE_NUM AS CLAIMBASEID,         
               PIWD.CLM_HDR_DMNSN.SPNSR_PPI AS SPONSORPPI,             
               PIWD.CLM_HDR_DMNSN.PTNT_ID_NUM AS PATIENTID,            
               PIWD.PTNT_GRPG_DMNSN.BENE_STUS_CD AS BENECAT,           
               PIWD.PTNT_GRPG_DMNSN.PTNT_GNDR_CD AS GENDER,            
               PIWD.PLAN_ADMINR_DMNSN.HOSP_PROF_SRVC_CD AS HOSPPROF,   
               PIWD.CLM_LINE_FACT.CLM_LINE_NUM AS CLMLINE,             
               PIWD.CLM_LINE_FACT.LINE_ALOWD_AMT AS ALLOWED,           
               PIWD.PLAN_ADMINR_DMNSN.DRVD_MCS_OPTN_IND AS MCSIND,     
               PIWD.CLNDR_TIME_DMNSN.CLNDR_MO_NAME AS MONTH,           
               PIWD.CLNDR_TIME_DMNSN.CLNDR_YR_NUM AS YEAR,             
               PIWD.DGNS_DMNSN.DGNS_1_CD AS DXCODE,                    
               A_ICD_DIAG_1.DGNS_SHRT_DESC AS DXDESC,                  
               PIWD.PRCDR_DMNSN.DRVD_PRCDR_CD AS PROCCODE,             
               PIWD.PRCDR_DMNSN.ICD_VRSN_IND AS ICD,                   
               PIWD.PRCDR_DMNSN.DRG_SRGCL_1_CD AS SURGCODE,            
               A_ICD_PROC_1.PRCDR_SHRT_DESC AS PROCDESC,               
               PIWD.PRCDR_DMNSN.REV_CD AS REVCODE,                     
               PIWD.MKT_AREA_DMNSN.AREA_NAME AS MKTAREA,               
               PIWD.MKT_AREA_DMNSN.MKT_NAME AS MARKET,                 
               PIWD.CLM_LINE_FACT.BILLED_AMT AS BILLED,                
                 PIWD.CLM_LINE_FACT.OHI_PD_AMT AS OHIPAID,              
                 PIWD.CLM_LINE_FACT.PD_AMT AS PAID,                     
                 PIWD.CLM_HDR_DMNSN.PTNT_BRTH_DT AS DOB,                
                 PIWD.CLNDR_TIME_DMNSN.FSCL_YR_NUM AS FY,               
                 PIWD.PTNT_GRPG_DMNSN.SPNSR_BOS_CD AS BOS,              
                 PIWD.CLM_LINE_FACT.NUM_SRVC_CNT AS NBRSVCS,            
                 PIWD.PLAN_ADMINR_DMNSN.HOME_PLAN_CD AS HOMEPLAN        
         FROM                                                           
           PIWD.CLM_HDR_DMNSN INNER JOIN PIWD.CLM_LINE_FACT ON          
         (PIWD.CLM_LINE_FACT.CLM_HDR_ID                                 
         =PIWD.CLM_HDR_DMNSN.CLM_HDR_ID)                                
            INNER JOIN PIWD.DGNS_DMNSN ON                               
         (PIWD.DGNS_DMNSN.DGNS_DMNSN_ID                                 
         =PIWD.CLM_LINE_FACT.DGNS_DMNSN_ID)                             
            LEFT OUTER JOIN PIWD.ICD_DGNS  A_ICD_DIAG_1 ON              
         (A_ICD_DIAG_1.ICD_VRSN_IND                                     
         =PIWD.DGNS_DMNSN.ICD_VRSN_IND AND                              
         A_ICD_DIAG_1.DGNS_UNFRMTTED_CD                                 
         =PIWD.DGNS_DMNSN.DGNS_1_CD)                                    
            INNER JOIN PIWD.CLNDR_TIME_DMNSN ON                         
         (PIWD.CLM_LINE_FACT.SRVC_DT_ID                                 
         =PIWD.CLNDR_TIME_DMNSN.CLNDR_TIME_ID)                          
            INNER JOIN PIWD.CLM_PCM_DMNSN ON                            
         (PIWD.CLM_PCM_DMNSN.CLM_PCM_DMNSN_ID                           
         =PIWD.CLM_LINE_FACT.CLM_PCM_DMNSN_ID)                          
            INNER JOIN PIWD.PLAN_ADMINR_DMNSN ON                        
         (PIWD.CLM_LINE_FACT.PLAN_ADMINR_ID                             
         =PIWD.PLAN_ADMINR_DMNSN.PLAN_ADMINR_ID)                        
          INNER JOIN PIWD.PRCDR_DMNSN ON                               
       (PIWD.CLM_LINE_FACT.PRCDR_DMNSN_ID                              
       =PIWD.PRCDR_DMNSN.PRCDR_DMNSN_ID)                               
          LEFT OUTER JOIN PIWD.ICD_PRCDR  A_ICD_PROC_1 ON              
       (PIWD.PRCDR_DMNSN.ICD_VRSN_IND                                  
       =A_ICD_PROC_1.ICD_VRSN_IND AND                                  
       PIWD.PRCDR_DMNSN.DRG_SRGCL_1_CD                                 
       =A_ICD_PROC_1.PRCDR_CD)                                         
          INNER JOIN PIWD.PTNT_GRPG_DMNSN ON                           
       (PIWD.CLM_LINE_FACT.PTNT_GRPG_ID                                
       =PIWD.PTNT_GRPG_DMNSN.PTNT_GRPG_ID)                             
          INNER JOIN PIWD.MKT_AREA_DMNSN ON                            
       (PIWD.MKT_AREA_DMNSN.MKT_AREA_DMNSN_ID                          
       =PIWD.CLM_LINE_FACT.BENE_MKT_AREA_DMNSN_ID)                     
                                                                       
    WHERE                                                              
       PIWD.CLM_PCM_DMNSN.JURSTCTN_DMIS_CD  IN  ('0056','1959','1660') 
       AND                                                             
       PIWD.CLNDR_TIME_DMNSN.CLNDR_DATE  BETWEEN  '2012-10-01'  AND    
    '2014-09-30'                                                       
       AND                                                             
       (                                                               
       PIWD.PRCDR_DMNSN.DRVD_PRCDR_CD  BETWEEN  '92920'  AND  '92929'  
       OR                                                              
       PIWD.PRCDR_DMNSN.DRVD_PRCDR_CD  BETWEEN  '92933'  AND  '92938'  
       OR                                                              
       PIWD.PRCDR_DMNSN.DRVD_PRCDR_CD  BETWEEN  '92941'  AND  '92943'  
       OR                                                              
           PIWD.PRCDR_DMNSN.DRVD_PRCDR_CD  BETWEEN  'C9600'  AND  'C9608'
           OR                                                            
           PIWD.PRCDR_DMNSN.DRVD_PRCDR_CD  BETWEEN  '93451'  AND  '93464'
           OR                                                            
           PIWD.PRCDR_DMNSN.DRG_SRGCL_1_CD  BETWEEN  '3721'  AND  '3723' 
           OR                                                            
           PIWD.PRCDR_DMNSN.DRG_SRGCL_1_CD  BETWEEN  '0040'  AND  '0048' 
           OR                                                            
           PIWD.PRCDR_DMNSN.DRG_SRGCL_1_CD  IN  ( '0055   ','0060   ',   
           '0066   ',                                                    
           '3606   ','3607   '  )                                        
              )                                                          
             )                                                           
            AND                                                          
            (                                                            
                  (PIWD.PLAN_ADMINR_DMNSN.HOSP_PROF_SRVC_CD = '1'        
                  _                                                      
                  _                                                      
                  _                                                      
                  22                                                     
                  22                                                     
                  22                                                     
                  76                                                     
                  76                                                     
                  76                                                     
 22-322: Expecting a name.                                               
 22-322: Expecting a name.                                               
 22-322: Expecting a name.                                               

    

 

Super Contributor
Super Contributor
Posts: 269

Re: Base sas

Was there a question, a description of the problem, and an explanation of the data in there or were you just posting random code?

 

Super User
Posts: 23,987

Re: Error in Pass through SQL - DB2

You made a mistake with the brackets.
Ask a Question
Discussion stats
  • 2 replies
  • 167 views
  • 0 likes
  • 3 in conversation