Desktop productivity for business analysts and programmers

ERROR: Variable EDIPN has been defined as both character and numeric

Reply
Frequent Contributor
Posts: 104

ERROR: Variable EDIPN has been defined as both character and numeric

 proc sql ;
200           CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");
201           CREATE TABLE HEDIS.ENROLL AS
202                SELECT * FROM CONNECTION TO tera
203        
204        (SELECT
205        
206             PAT_ENR_HIST.SPNSR_SSN AS SPONSSN,
207             PAT_ENR_HIST.PTNT_ID AS PTNTID,
208             PATIENT.DEERS_PTNT_ID AS EDIPN,
209             PAT_PCM_HIST.PCM_PROV_ID,
210             PAT_PCM_HIST.PCM_THRU_DT,
211             PAT_ENR_HIST.HCDP_CD,
212             PAT_ENR_HIST.BENE_BIRTH_DT,
6                                                          The SAS System                                08:48 Tuesday, May 29, 2018

213             PAT_ENR_HIST.ENRL_EFF_DT,
214             PAT_ENR_HIST.UPDT_DT,
215             PAT_ENR_HIST.BENE_GNDR_CD,
216             PAT_ENR_HIST.DISENRL_DT,
217             PATIENT.DEERS_BENE_ID AS DEERSBENEID,
218             PATIENT.DEERS_FMLY_ID AS DEERSFMLYID
219          FROM
220           PATIENT RIGHT OUTER JOIN PAT_ENR_HIST
221             ON (PAT_ENR_HIST.PTNT_ID=PATIENT.PTNT_ID
222                  AND PAT_ENR_HIST.SPNSR_PPI=PATIENT.SPNSR_PPI)
223             INNER JOIN PAT_PCM_HIST
224            ON (PAT_ENR_HIST.PTNT_ID=PAT_PCM_HIST.PTNT_ID
225             AND PAT_ENR_HIST.ENRL_EFF_DT=PAT_PCM_HIST.ENRL_EFF_DT
226             AND PAT_ENR_HIST.SPNSR_PPI=PAT_PCM_HIST.SPNSR_PPI)
227          WHERE
228            PAT_ENR_HIST.BENE_BIRTH_DT  BETWEEN  '1953-02-01' and '1999-02-01' /*18-64 yr olds*/
229              AND PAT_PCM_HIST.PROV_LCTR_CD = '01'
230          );
NOTE: Table HEDIS.ENROLL created, with 222939 rows and 13 columns.

231          %PUT &SQLXMSG &SQLXRC;
0
232          QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.11 seconds
      cpu time            0.31 seconds
      

233        
234        
235        ** PER JIM ELIMINATE TYA ENROLLMENT **;
236        DATA HEDIS.ENROLL1;
237          SET HEDIS.ENROLL;
238          ATTRIB BENEID LENGTH = $12 FORMAT=$12. INFORMAT=$12.;
239             IF HCDP_CD IN ('422','423','424','425','426','427','428',
240                            '429','430') THEN DELETE;
241             BENEID = TRIM(SPONSSN) !! TRIM(PTNTID); /*REMOVED UNDERSCORE, CREATED ALIAS IN ENROLL PULL*/
242             BENE = TRIM(EDIPN); /*EDIPN - CHANGED NAME FROM DEERS_PTNT_ID*/
243        RUN;

  proc sql ;
275           CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");
276           CREATE TABLE HEDIS.CLMSPULL AS
277                SELECT * FROM CONNECTION TO tera
278        
279        (SELECT
280        
281               CLM_HDR_DMNSN.CLM_BASE_NUM          AS CLAIMBASEID,
282                CLM_LINE_FACT.CLM_LINE_NUM          AS CLAIMLINE,
283                CLM_HDR_DMNSN.CLM_ADJ_ID            AS CLAIMADJID,
284                CLM_HDR_DMNSN.PTNT_BRTH_DT          AS DOB,
285                CLM_HDR_DMNSN.CLM_BGN_SRVC_DT       AS CLMBEGINDATE,
286                CLNDR_TIME_DMNSN.CLNDR_DATE         AS CLAIMSERVICEDATE,
287                CLM_HDR_DMNSN.SPNSR_SSN             AS SPONSSN,
288                CLM_HDR_DMNSN.PTNT_ID_NUM           AS PTNTID,
289        	    CLM_HDR_DMNSN.DEERS_UNIQ_PTNT_ID    AS EDIPN, /*renamed to EDIPN*/
290        	    CLM_HDR_DMNSN.DEERS_BENE_ID         AS DEERSBENEID,
291                CLM_HDR_DMNSN.DEERS_FMLY_ID         AS DEERSFMLY,
292                DGNS_DMNSN.DGNS_1_CD                AS DIAG1,
293                DGNS_DMNSN.DGNS_2_CD                AS DIAG2,
294                DGNS_DMNSN.DGNS_3_CD                AS DIAG3,
295                DGNS_DMNSN.DGNS_4_CD                AS DIAG4,
296                DGNS_DMNSN.DGNS_5_CD                AS DIAG5,
8                                                          The SAS System                                08:48 Tuesday, May 29, 2018

297                DGNS_DMNSN.DGNS_6_CD                AS DIAG6,
298                DGNS_DMNSN.DGNS_7_CD                AS DIAG7,
299                DGNS_DMNSN.DGNS_8_CD                AS DIAG8,
300                DGNS_DMNSN.DGNS_9_CD                AS DIAG9,
301                DGNS_DMNSN.DGNS_10_CD               AS DIAG10,
302                PRCDR_DMNSN.CPT4_PRCDR_CD           AS CPT4CODE,
303                PRCDR_DMNSN.REV_CD                  AS REV,
304                PRCDR_DMNSN.HCPCS_PRCDR_CD          AS HCPCSCODE,
305                PRCDR_DMNSN.NDC_CD                  AS NDC,
306                PRCDR_DMNSN.DRVD_PRCDR_CD           AS PROCEDURECODE,
307                PRCDR_DMNSN.ICD_VRSN_IND            AS ICDIND,
308                CLM_LINE_FACT.OHI_PD_AMT            AS OHIPAIDAMT,
309                PLAN_ADMINR_DMNSN.RSRC_SHRG_IND     AS RESOURCESHARE,
310                PLAN_ADMINR_DMNSN.DRVD_MCS_OPTN_IND AS MCSOPTIONIND,
311                PLAN_ADMINR_DMNSN.HOME_PLAN_CD AS PRIMEMTFCIVSTDEXT,
312               CLM_LINE_FACT.LINE_ALOWD_AMT        AS ALLOWEDAMT,
313               CLM_LINE_FACT.PD_AMT                AS PDAMT,
314        		CLM_DMNSN.POS_CD 					AS POS,
315        		CLM_LINE_FACT.DRUG_QTY				AS DRUGQTY

384         /*Excludes the provided list of diabetics from claims and enrollment*/
385        DATA HEDIS.CLMS_NDC3;
386         MERGE HEDIS.diabexc (IN=A keep=EDIPN) /*just look up on edipn*/
387         	   HEDIS.CLMS_NDC2 (IN=B);
ERROR: Variable EDIPN has been defined as both character and numeric.
388        EDIPN = PUT('EDIPN'n,$12.);
WARNING: Variable EDIPN has already been defined as numeric.
389        BY EDIPN;
390        IF B and not A;
391        RUN;
Super User
Posts: 9,932

Re: ERROR: Variable EDIPN has been defined as both character and numeric

This statement

EDIPN = PUT('EDIPN'n,$12.);

is nonsense. If you need to change a variable's type, you have to create a new variable in a preceding step, in your lookup dataset.

Changing it while you need it for merging is counter-productive.

See the many posts here about changing variable types.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 104

Re: ERROR: Variable EDIPN has been defined as both character and numeric

Posted in reply to KurtBremser
Thank you. I still get the error even without changing to numeric.
Super User
Posts: 9,932

Re: ERROR: Variable EDIPN has been defined as both character and numeric

You have to make sure that edipn is of the same type in BOTH datasets used in the merge.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 3 replies
  • 85 views
  • 0 likes
  • 2 in conversation