SAS Proc format Error's

[foxrol94]

Hi,

I have one table called ENC (same table are in file attached):

PAYS   | Header |CD2  |VAL1|VAL2| COEF

BRE    | MATLAB | 11  |.   |500 |-0,9561
BRE    | MATLAB | 11  |500 |.   |0
BRE    | MATLAB | 2   |.   |550 |-1,2212
BRE    | MATLAB | 2   |550 |.   |0

and I would like to create a SAS Format by using this code(missing signify to be "lowest or highest possible value") :

data WORK.FMT_ENC(keep=START END FMTNAME LABEL);
    length FMTNAME $32;
    retain FMTNAME ;
    set ENC (rename=(VAL1=start VAL2=end   COEF=label)) end=eof;
    FMTNAME = lowcase(compress('$' !! PAYS !! Header !! put(CD2,2.)!!'_flgperso'));
    /*retain fmtname "test" type "C";*/
    output;
    if eof then
    do;
        hlo = "O";
        label = "Autre";
        output;
    end;
    run;
    proc format library=WORK cntlin=WORK.FMT_ENC;
run;

and I get this error:

ERROR: Cannot mix missing and nonmissing values in the same range: .-500.

Plase could you tell me what is wrong in my code.

Thanks a lot fro your reply.

[Shmuel]

If val1 = . define it as "LOW";

if val2 = . define it as "HIGH";

That means that you need define START and END fields as alphanumeric, eventhough

you want to creat FORMAT and not FORMATC;

[foxrol94]

Hi,

How can i do that?

Thnaks

[Shmuel]

Your code with changes:

data WORK.FMT_ENC(keep=START END FMTNAME LABEL);
    length FMTNAME $32;
    retain FMTNAME ;
    set ENC (rename=(COEF=label)) end=eof;
        length start end $16;
        if VAL1 = . then start='LOW'; else start=left(VAL1);
        if val2 = . then end = 'HIGH"; else end = left(VAL2);

    FMTNAME = lowcase(compress('$' !! PAYS !! Header !! put(CD2,2.)!!'_flgperso'));
    /*retain fmtname "test" type "C";*/
    output;
    if eof then
    do;
        hlo = "O";
        label = "Autre";
        output;
    end;
    run;
    proc format library=WORK cntlin=WORK.FMT_ENC;
run;

[Astounding]

The error message tells you one of the problems.  But even if you were to fix that one, there would be more.  The ranges in your format appear to overlap.

Try sketching out the complete VALUE statement that would be equivalent to what you want PROC FORMAT to execute for this data.  That would clarify enough of your intentions that the code can be fixed.

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Better for posting example data is data step code to allow recreating your example data set. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...