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

[qchac]

hello.


I have the following error when I've tried to run the program.


ERROR: Variable COD_PRD has been defined as both character and numeric.


Can anybody help me ?

Thanks.

program:



............................................................


rsubmit &r_unix ;
/*extraction des données de la Fidx111t */
proc sql;
connect to db2(&db);
create view evt111 as select num_prog length=8, num_adh, num_scp length=6, num_evt length=6, typ_evtv length=4,
val_evt length=4, val_pue length=4, date_cre, date_evt, mt_eu length=6, mt_de length=6, qte length=4, nbr_ue length=6,
unit_mes, num_ent, num_prd length=6, cod_prov length=4, val_pai length=4, cod_oevt length=4, sig_cre, cod_prd length=5
from connection to db2
/* modif E-Mice1284 04-07-2006 ajouter l'extraction des 5 premiers caractères de la variable COD_PRD */
(select a.num_prog, a.num_adh, a.num_scp, a.num_evt, a.typ_evtv, a.val_evt, a.val_pue, a.date_cre, a.date_evt,
a.mt_eu, a.mt_de, a.qte, a.unit_mes, a.nbr_ue, a.num_ent, a.num_prd, a.cod_prov, a.val_pai, a.cod_oevt, a.sig_cre,
b.cod_prd
from &db2fid.fid111v2 a
left join &db2fid.fidx121t b on a.num_prog=b.num_prog and a.num_adh=b.num_adh and
a.num_scp= b.num_scp and a.num_evt=b.num_evt
/* fin modif ***********************************************************/
where a.num_prog in (&listeprg) and a.date_cre between &dbdebsel and &dbfinsel) order by num_prog, val_pue;
%let rc=&sqlxrc;
%let msg=&sqlxmsg;
disconnect from db2;
quit;

/* extraction de la table des paramètres avec VAL_PUE*/

/* Début Modif GP 19/12/2003 : on crée une table au lieu d'une vue */
/* on supprime l'order by VAL_PUE dans la proc sql */
/* on trie ensuite la table créée de manière unique par VAL_PUE */

proc sql;
connect to db2(&db);
create table fidx004t as select * from connection to db2
(select num_prog, cod_val as num_op ,num_val as val_pue
from &db2fid.fid004v3
where num_prog in (&listeprg) and cod_par='VAL_PUE');
%let rc=&sqlxrc;
%let msg=&sqlxmsg;
disconnect from db2;
quit;

proc sort data=fidx004t nodupkey;
by NUM_PROG VAL_PUE;
run;

/* Fin Modif GP 19/12/2003 */

endrsubmit ;

/* ajout de la variable num_op */

/* Modif GP 03/11/2003 : mise en commentaire du 'drop' de la variable MT_DE */
data evt112 /* (drop=mt_de) */ ;
/* Modif GRI 30/12/2005 : Modif longueur zone NUM_PROG, DATE_CRE, DATE_EVT, NUM_ENT, UNIT_MES */
/* modif E-Mice1284 04-07-2006 ajouter la définition de COD_PRD */
/* changer la longueur de MT_DE de 8 à 6 */
attrib NUM_PROG length=4 NUM_SCP length=6 NUM_EVT length=6 TYP_EVTV length=4
VAL_EVT length=4 VAL_PUE length=4 MT_EU length=6 MT_DE length=6
QTE length=4 NBR_UE length=6
NUM_PRD length=6 COD_PROV length=4 VAL_PAI length=4
DATE_CRE length=6 DATE_EVT length=6 NUM_ENT length=6 UNIT_MES length=6
COD_OEVT length=4 COD_PRD length=5;
/* fin modif ***********************************************************/
merge r99d1.fidx004t(in=x) r99d1.evt111(in=y);
by num_prog val_pue;
if y=1 then
do;
/* Modif GP 03/11/2003 : mise en commentaire du recalcul de la variable MT_EU */
/*
if mt_eu eq 0 and mt_de ne 0 then mt_eu=round((mt_de/6.55957),0.01);
else mt_eu=round(mt_eu,0.01);
*/
output;
end;
else delete;
run;

/*ajout de la table des recyclées*/

%if %sysfunc(exist(recycle.FIWM010R,data)) > 0 %then %do ;
proc append base=evt112 data=recycle.FIWM010R(drop=ano dat_ano);
run;

%end;

/* ajout du clicont */

proc sort data= evt112;by num_prog num_adh num_scp;run;

data evt113 (drop=nb_ano ano dat_ano num_adh num_scp index=(CLICONT=(NUM_PROG CLI CONT)))
recycle.FIWM010R (drop=nb_ano cli cont dat_crea label='Table des Recyclés événements valorisables')
rejet1 (keep=ano dat_ano nb_ano);
retain nb_ano 0;
length ano $50;
format dat_ano DATE_EVT DATE_CRE ddmmyy10.;
merge evt112 (in=x)
donnees.fiwa010t (in=y keep=num_prog cli cont num_adh num_scp)
pilote.fiwa010p (in=z keep=num_prog num_adh num_scp where=(num_prog ne . and num_adh ne . and num_scp ne .))
end=fin;
by num_prog num_adh num_scp;
/*correction des dates manquantes*/
if date_cre=. then date_cre=-21549;
if date_evt=. then date_evt=-21549;
/* Modif PIR 15-12-2004 Optimisation d'espace disque */
/* diminuer la longueur des zones numerique */
length num_prog 4 unit_mes num_ent date_cre date_evt dat_crea 6 ;
if x=1 then
do;
if y=1 then
output evt113;
else
do;
if z=0 then
do;
ano='num adh inconnu';
dat_ano=&fiwdate;
output recycle.FIWM010R ;
end;
else
do;
nb_ano=nb_ano+1;
end;
end;
end;
if fin=1 then
do;
dat_ano=&fiwdate;
ano='num_adh num_scp non valide';
output rejet1;
end;
run;

/* Ajout à la table des anomalies */
Proc append base=rejets.FIWm010x(label='Table des Rejets événements valorisables') data=rejet1; run;

/********************************/
/* séparation événements casino */
/********************************/

proc sort data=pilote.fiwx010t; by num_prog cli cont; run;

data evt113
evt113c;
merge evt113 (in=a)
pilote.fiwx010t (in=b);
by num_prog cli cont;
if a=1 then
do;
if b=0 then
output evt113;
else
output evt113c;
end;
run;

/* création des 3tables des 3 dernieres annees et de la table des anomalies correspondant avec les années inferieures*/
/* fabrication du nom des tables le dernier digit numerique exprime l'année en cours ex: 5 ==> 2005 */

data _null_;
an0=substr(put(year(&fiwdate),4.),4,1);
an1=substr(put(year(&fiwdate)-1,4.),4,1);
an2=substr(put(year(&fiwdate)-2,4.),4,1);
call symput('an0',trim(an0));
call symput('an1',trim(an1));
call symput('an2',trim(an2));
call symput('an3','a');
run;

/* création des 3 tables des 3 dernieres annees et de la table des anomalies correspondant avec les années inferieures*/

data FIWm01&an0.t
FIWm01&an1.t
FIWm01&an2.t
FIWm01&an3.t;
format DAT_CREA ddmmyy10.;
set evt113;
DAT_CREA=&fiwdate;
/* modif E-Mice1284 04-07-2006 passer SIG_CREA à 3 de long, et changer les affectations SIG_CREA='M10'(au lieu de FIWM10') */
SIG_CREA='M10';
/* fin modif ***********************************************************/
if year(date_evt)=year(&fiwdate) then output FIWm01&an0.t;
else if year(date_evt)=year(&fiwdate)-1 then output FIWm01&an1.t;
else if year(date_evt)=year(&fiwdate)-2 then output FIWm01&an2.t;
else output FIWm01&an3.t;
run;

options compress=&compress;

/* Ajout aux tables du DWH - NORMAL */
/* Si 1ère création de la table alors ajout d'un index */

%if %sysfunc(exist(donnees.FIWm01&an0.t)) eq 0 %then
%do;
Proc append base=donnees.FIWm01&an0.t data= FIWm01&an0.t ;run;

Proc datasets lib=donnees nolist;
modify FIWm01&an0.t (label="Table des événements valorisables an &an0");
index create PROGEVT=(NUM_PROG NUM_EVT);
index create CLICONT=(NUM_PROG CLI CONT);
run;quit;
%end;
%else
%do;
Proc append base=donnees.FIWm01&an0.t data= FIWm01&an0.t ;run;
%end;

%if %sysfunc(exist(donnees.FIWm01&an1.t)) eq 0 %then
%do;
Proc append base=donnees.FIWm01&an1.t data= FIWm01&an1.t ;run;

Proc datasets lib=donnees nolist;
modify FIWm01&an1.t (label="Table des événements valorisables an &an1") ;
index create PROGEVT=(NUM_PROG NUM_EVT);
index create CLICONT=(NUM_PROG CLI CONT);
run;quit;
%end;
%else
%do;
Proc append base=donnees.FIWm01&an1.t data=FIWm01&an1.t ;run;
%end;

%if %sysfunc(exist(donnees.FIWm01&an2.t)) eq 0 %then
%do;
Proc append base=donnees.FIWm01&an2.t data=FIWm01&an2.t ;run;

Proc datasets lib=donnees nolist;
modify FIWm01&an2.t (label="Table des événements valorisables an &an2") ;
index create PROGEVT=(NUM_PROG NUM_EVT);
index create CLICONT=(NUM_PROG CLI CONT);
run;quit;
%end;
%else
%do;
Proc append base=donnees.FIWm01&an2.t data= FIWm01&an2.t ;run;
%end;

%if %sysfunc(exist(donnees.FIWm01&an3.t)) eq 0 %then
%do;
Proc append base=donnees.FIWm01&an3.t data=FIWm01&an3.t ;run;

Proc datasets lib=donnees nolist;
modify FIWm01&an3.t (label="Table des événements valorisables an &an3") ;
index create PROGEVT=(NUM_PROG NUM_EVT);
index create CLICONT=(NUM_PROG CLI CONT);
run;quit;
%end;
%else
%do;
Proc append base=donnees.FIWm01&an3.t data= FIWm01&an3.t ;run;
%end;

options compress=no;

/* création des 3 tables des 3 dernieres annees et de la table des anomalies correspondant avec les années inferieures*/

data FIWm01&an0.c
FIWm01&an1.c
FIWm01&an2.c
FIWm01&an3.c;
format DAT_CREA ddmmyy10.;
set evt113c;
DAT_CREA=&fiwdate;
SIG_CREA='M10';
if year(date_evt)=year(&fiwdate) then output FIWm01&an0.c;
else if year(date_evt)=year(&fiwdate)-1 then output FIWm01&an1.c;
else if year(date_evt)=year(&fiwdate)-2 then output FIWm01&an2.c;
else output FIWm01&an3.c;
run;

options compress=&compress;

/* Ajout aux tables du DWH - CASINO */
/* Si 1ère création de la table alors ajout d'un index */

%if %sysfunc(exist(donnees.FIWm01&an0.c)) eq 0 %then
%do;
Proc append base=donnees.FIWm01&an0.c data= FIWm01&an0.c ;run;

Proc datasets lib=donnees nolist;
modify FIWm01&an0.c (label="Table des événements valorisables adhérent Casino an &an0");
index create PROGEVT=(NUM_PROG NUM_EVT);
run;quit;
%end;
%else
%do;
Proc append base=donnees.FIWm01&an0.c data= FIWm01&an0.c ;run;
%end;

%if %sysfunc(exist(donnees.FIWm01&an1.c)) eq 0 %then
%do;
Proc append base=donnees.FIWm01&an1.c data= FIWm01&an1.c ;run;

Proc datasets lib=donnees nolist;
modify FIWm01&an1.c (label="Table des événements valorisables adhérent Casino an &an1") ;
index create PROGEVT=(NUM_PROG NUM_EVT);
run;quit;
%end;
%else
%do;
Proc append base=donnees.FIWm01&an1.c data=FIWm01&an1.c ;run;
%end;

%if %sysfunc(exist(donnees.FIWm01&an2.c)) eq 0 %then
%do;
Proc append base=donnees.FIWm01&an2.c data=FIWm01&an2.c ;run;

Proc datasets lib=donnees nolist;
modify FIWm01&an2.c (label="Table des événements valorisables adhérent Casino an &an2") ;
index create PROGEVT=(NUM_PROG NUM_EVT);
run;quit;
%end;
%else
%do;
Proc append base=donnees.FIWm01&an2.c data= FIWm01&an2.c ;run;
%end;

%if %sysfunc(exist(donnees.FIWm01&an3.c)) eq 0 %then
%do;
Proc append base=donnees.FIWm01&an3.c data=FIWm01&an3.c ;run;

Proc datasets lib=donnees nolist;
modify FIWm01&an3.c (label="Table des événements valorisables adhérent Casino an &an3") ;
index create PROGEVT=(NUM_PROG NUM_EVT);
run;quit;

%end;
%else
%do;
Proc append base=donnees.FIWm01&an3.c data= FIWm01&an3.c ;run;
%end;

options compress=no;

%FIWXDATE(type=U, traitmt=FIWM10, statut=OK);


............................................................

 

[ChrisW75]

Two things to help us help you.
1 - When submitting code, use the (code) ... (/code),tags around the code, replacing the brackets with square brackets, it formats the code properly.
2 - show us where in that 300+ lines of code the error actually occurs.

The common causes of this error message are as follows:-
- Joining two tables together with the variable on oth, declared as a numeric on one and a character on the other.
- Performing a character function on a variable that doesn't exist. when this happens, SAS creates the variable, and by default a new variable is a numeric field, this often causes this error message.

 

[qchac]

sry chris.

I'd to put it fast and I've forgotten the elementary rules.

33                               The SAS System     17:41 Thursday, July 6, 2006

length=6 COD_OEVT length=4 COD_PRD length=5;
MPRINT(ACHATS):   merge r99d1.fidx004t(in=x) r99d1.evt111(in=y);
ERROR: Variable COD_PRD has been defined as both character and numeric.
ERROR: Variable COD_PRD has been defined as both character and numeric.
ERROR: Variable COD_PRD has been defined as both character and numeric.
MPRINT(ACHATS):   by num_prog val_pue;
MPRINT(ACHATS):   if y=1 then do;

thanks again for everything.

 

[ChrisW75]

No worries .
OK, that step where the error occurs, you have some attrib statements to set up lengths of variables, however, you are reading SAS datasets, where these lengths should already have been set, I would stay away from setting lengths on datasteps where the column already exists.

To debug this, you need to look at the two tables you are joining together, r99d1.fidx004t and r99d1.evt111. one of these two datasets probably already has the variable COD_PRD on it, defined as a character variable.

I believe we had a very similar discussion in this thread...

http://www.tek-tips.com/viewthread.cfm?qid=1247697&page=1

Think of it like this - you cannot change the length of a variable once it has been set, all you can do is create a new variable that is the correct size, and put the values from the original column into that.
Don't use Length or Attrib statements in steps unless you are creating new fields, they are there only for that purpose.

 

[qchac]

you are right, there is COD_PRD defined as char in fidx121t.

I had removed the length and now it looks like this:

....
create view evt111 as select num_prog length=8, num_adh, num_scp length=6, num_evt length=6, typ_evtv length=4,
        val_evt length=4, val_pue length=4, date_cre, date_evt, mt_eu length=6, mt_de length=6, qte length=4, nbr_ue length=6,
        unit_mes, num_ent, num_prd length=6, cod_prov length=4, val_pai length=4, cod_oevt length=4, sig_cre, cod_prd
....

i don't know why there is still the old code (with the length after the COD_PRD) in the log:


MPRINT(ACHATS): attrib NUM_PROG length=4 NUM_SCP length=6 NUM_EVT length=6
TYP_EVTV length=4 VAL_EVT length=4 VAL_PUE length=4 MT_EU length=6 MT_DE
length=6 QTE length=4 NBR_UE length=6 NUM_PRD length=6 COD_PROV length=4
VAL_PAI length=4 DATE_CRE length=6 DATE_EVT length=6 NUM_ENT length=6 UNIT_MES
33 The SAS System 09:31 Friday, July 7, 2006

length=6 COD_OEVT length=4 COD_PRD length=5;
MPRINT(ACHATS): merge r99d1.fidx004t(in=x) r99d1.evt111(in=y);
ERROR: Variable COD_PRD has been defined as both character and numeric.
ERROR: Variable COD_PRD has been defined as both character and numeric.
ERROR: Variable COD_PRD has been defined as both character and numeric.
MPRINT(ACHATS): by num_prog val_pue;


Raul.

 

[ChrisW75]

Make sure you re-run the macro definition after changing it.

 

[qchac]

I've set COD_PRG as char in the attrib instruction and I eliminated the length in the sql. Thanks Chris.

The problem now is with the message (I knew this will appear when trying to rewrite the same dataset while
trying to read it at the same time, but I don't have this situation here):

MPRINT(ACHATS):   options compress=no;
MPRINT(ACHATS):   data FIWm016c FIWm015c FIWm014c FIWm01ac;
MPRINT(ACHATS):   format DAT_CREA ddmmyy10.;
MPRINT(ACHATS):   set evt113c;
MPRINT(ACHATS):   DAT_CREA= 16989;
MPRINT(ACHATS):   SIG_CREA='M10';
MPRINT(ACHATS):   if year(date_evt)=year( 16989) then output FIWm016c;
MPRINT(ACHATS):   else if year(date_evt)=year( 16989)-1 then output FIWm015c;
MPRINT(ACHATS):   else if year(date_evt)=year( 16989)-2 then output FIWm014c;
MPRINT(ACHATS):   else output FIWm01ac;
MPRINT(ACHATS):   run;

NOTE: There were 0 observations read from the data set WORK.EVT113C.
NOTE: The data set WORK.FIWM016C has 0 observations and 24 variables.
NOTE: The data set WORK.FIWM015C has 0 observations and 24 variables.
NOTE: The data set WORK.FIWM014C has 0 observations and 24 variables.
NOTE: The data set WORK.FIWM01AC has 0 observations and 24 variables.
NOTE: DATA statement used:
      real time           0.12 seconds
      cpu time            0.01 seconds
      
42                               The SAS System       11:28 Friday, July 7, 2006


MPRINT(ACHATS):   options compress=yes;
ERROR: Member or library DONNEES.FIWM016C.DATA unavailable for use.
ERROR: Member or library DONNEES.FIWM016C.DATA unavailable for use.
ERROR: Member or library DONNEES.FIWM016C.DATA unavailable for use.													
MPRINT(ACHATS):   Proc append base=donnees.FIWm016c data= FIWm016c ;
MPRINT(ACHATS):  run;

NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used:
      real time           0.01 seconds
      cpu time            0.00 seconds

Raul.