length problem 1

[qchac]

hi.

I have to change the length of the variables SIG_CREA and SIG_MOD from 6 to 3 characters. The problem is that the length is still 6 and I don't know why.

thx.

the program:

options mprint symbolgen mlogic;

/* mise à jour table de pilotage */
%FIWXDATE(type=C, traitmt=FIWM60, statut=DB, etape=, info_lue=0, info_rep=0, info_rej=0, info_ecr=0);

/* modif E-Mice1516 26-06-2006 calculer la macro variable variable &finsel_j_db2 */
/* Ajout de la variable macro finsel_j pour une date de fin d'extraction à J */
%global finsel_j;
%let finsel_j=%sysevalf(&fifinsel+2);
%syslput finsel_j=%sysevalf(&fifinsel+2);
/* fin modif ***********************************************************/

/*liaison avec r99d1 - r55 */
%syslput1(db,&db,remote=&r_unix);
%syslput1(db2fid,&db2fid,remote=&r_unix);
%syslput1(dbdebsel,&dbdebsel,remote=&r_unix);
%syslput1(dbfinsel,&dbfinsel,remote=&r_unix);
%syslput1(numprg,&numprg,remote=&r_unix);
%syslput1(listeprg,&listeprg,remote=&r_unix);

%macro compteur;

%global NB_LUE NB_REJ NB_ECR;

rsubmit &r_unix ;
%let sqlxrc=0;
%let sqlxmsg=;

/* modif E-Mice1516 26-06-2006 mise au format DB2 de la date finsel_j => finsel_j_db2 */
data _null_;
call symput ('FINSEL_J_DB2',"'"||put(&FINSEL_J,yymmdd10.)||"'");
run;
/* fin modif ***********************************************************/

/* recuperation des informations dans la table FIDX108T */
/* remplacer la table par la vue */
proc sql;
connect to db2(&db);
create view tmpmil01 as select * from connection to db2
(select NUM_PROG, NUM_ADH,NUM_SCP,NUM_MIL,NBR_UEUT,NBR_UECO,NBR_UEPE,
/* modif E-Mice1516 26-06-2006 ajouter la variable DATE_MAJ */
/* dans l'extraction DB2 de la table fidx109v3 */
DATE_MAJ
/* fin modif ***********************************************************/
from &db2fid.fid109v3
where NUM_PROG in (&listeprg)
/* modif E-Mice1516 26-06-2006 rajouter la macro variable &finsel_j_db2 */
/* en borne supérieur de DAT_MAJ) */
and DATE_MAJ between &dbdebsel and &finsel_j_db2);
/* fin modif ***********************************************************/
%let rc=&sqlxrc;
%let msg=&sqlxmsg;
disconnect from db2;
quit;
/* affichage du message SQL si erreur de connection*/
%if &rc ne 0 %then %do;
%put &rc ;
%put &msg;
%end ;
endrsubmit;

/* copie de la table dans la work */
data tmpmil01;
set r99d1.tmpmil01;
run;

/* Comptage pour suivi */
proc sql noprint;
select nobs into: NB_LUE
from sashelp.vtable
where libname='WORK' and memname='TMPMIL01';
quit;

proc sort data=tmpmil01;by NUM_PROG NUM_ADH NUM_SCP NUM_MIL;run;

/* mise à jour des recyclés */

%if %sysfunc(exist(recycle.fiwm060r)) ne 0 %then %do;
proc sort data=recycle.fiwm060r;
by NUM_PROG NUM_ADH NUM_SCP NUM_MIL;
run;

data tmpmil01;
update recycle.fiwm060r (drop=ANO DAT_ANO) tmpmil01;
by NUM_PROG NUM_ADH NUM_SCP NUM_MIL;
run;
%end;

/*gestion des recyclés et des rejets */

data tmpmil02 (drop=NUM_ADH NUM_SCP ANO DAT_ANO NB_ANO)
recycle.fiwm060r (drop=CLI CONT NB_ANO label='Table des recyclés Compteurs')
fiwm060x (keep=ANO DAT_ANO NB_ANO);
retain NB_ANO 0;
format ANO $50. DAT_ANO ddmmyy10.;

/* modif E-Mice1516 26-06-2006 ajouter une instruction ATTRIB */
/* pour préciser le format label et longueur des 3 variables. */
attrib DATE_MAJ label="Date de mise à jour FidWare" length=6 format=ddmmyy10. informat=DATE9.
SIG_CREA length=$3
SIG_MOD length=$3
; [/color red]
/* fin modif ***********************************************************/

merge tmpmil01 (in=x)
donnees.fiwa010t (in=y keep=NUM_PROG NUM_ADH NUM_SCP CLI CONT)
pilote.fiwa010p (in=z keep=NUM_PROG NUM_ADH NUM_SCP where=(NUM_ADH ne . and NUM_SCP ne .)) /* cli cont non valide */
end=fin;
by NUM_PROG NUM_ADH NUM_SCP;
if x=1 then do;
if y=1 then output tmpmil02;
else do;
if z=1 then do;
NB_ANO=NB_ANO+1;
end;
else do;
DAT_ANO=&fiwdate;
ANO='num_prog num_adh num_scp inconnu';
output recycle.fiwm060r;
end;
end;
end;
if fin=1 then do;
call symput ('NB_REJ',trim(left(put(NB_ANO,best.))));
DAT_ANO=&fiwdate;
ANO='cli cont non valide';
output fiwm060x;
end;
run;

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

 

[klaz2002]

You cant change the length of a SAS var once its set. In your case the var that is read from one of your merge datasets have the length 6 already set for variable that you want to change. That said, you can change the length by tricking the data step by renaming the variable that you want to reset. Then you use the regular length statement to set the length that you like. Finaly, you set the old value to this 'new' variable.
Note that SAS will truncate all longer strings than your new size (3).

I hope that this helps you.
Klaz

 

[qchac]

Klaz,

First thx for your help.
I've added the following instructions in merge:

rename SIG_CREA = SIG_CREA_TMP SIG_MOD = SIG_MOD_TMP;
length SIG_CREA_TMP $3 SIG_MOD_TMP $3;
SIG_CREA = SIG_CREA_TMP;
SIG_MOD = SIG_MOD_TMP;
drop SIG_CREA_TMP SIG_MOD_TMP;
format SIG_CREA $3. SIG_MOD $3.;

The problem is that the length of SIG_CREA and SIG_MOD is still 6, the format is changed to $3. This is the structure :

http://static.flickr.com/60/176978531_47a441082f.jpg?v=0

[...] SIG_CREA SIG_MOD DATE_MOD SIG_CRE SIG_CREA_TEMP SIG_MOD_TMP

where SIG_CREA SIG_MOD DATE_MOD (are the columns that I want) and SIG_CRE is a mistake (I've used a variable called like this and the column remained) and both _TMP are the variables used in upper code.

Is the code ok and how can I make the unwanted columns disappear ?

thx, Seb.

 

[ChrisW75]

try something like this:-

data outdat(drop=SIG_CREA_TMP SIG_MOD_TMP);
   set indat(rename=(SIG_CREA = SIG_CREA_TMP
                     SIG_MOD = SIG_MOD_TMP));

   length SIG_CREA_TMP $3 SIG_MOD_TMP $3;

   SIG_CREA = SIG_CREA_TMP;
   SIG_MOD = SIG_MOD_TMP;
run;

The problem is that in your version, the variables had already been read in, so the length was already set, even though you renamed them. This should fix it I would hope.