12-07-2016 10:58 AM - edited 12-07-2016 11:16 AM
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.
12-07-2016 11:11 AM
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;
12-07-2016 11:29 AM
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;
12-07-2016 11:12 AM
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.
12-07-2016 11:20 AM
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...