Proc Format using "Cntlin", What I am doing wrong!!

[SASuserlot]

I want to create a numeric format for a character variable ( create a new numeric variable based on character data) using proc format 'Cntlin', I am doing something wrong. I tried a couple of things, but I am ending up format error in the log, or it just creating character to character.  What is the easy way to create numeric variable using proc formats.

Data:

 data format_from;
 test ='ALT';
 testn =1;
 output;
 test ='UR';
 testn =2;
 output;
 test ='KG';
 testn =3;
output;
run;

data have;
 test ='ALT';
 output;
 test ='UR';
 output;
 test ='KG';
output;
 test ='MG';
output;
run;

attempt1 :

data work.outfmtn(keep=start label fmtname); 
    set work.format_from(rename=(test= start  testn=  label));  
    fmtname='Num';
	type = 'N';
run;

proc sort data=outfmtn nodupkey;
    by fmtname start;
run;

proc format library=work cntlin=work.outfmtn;
quit;

data want;
set have;
numeric_test = input(test,num.);
run;

Log error:

777
778
779  data work.outfmtn(keep=start label fmtname);
780      set work.format_from(rename=(test= start  testn=  label));
781      fmtname='Num';
782      type = 'N';
783  run;

NOTE: There were 3 observations read from the data set WORK.FORMAT_FROM.
NOTE: The data set WORK.OUTFMTN has 3 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


784
785  proc sort data=outfmtn nodupkey;
786      by fmtname start;
787  run;

NOTE: There were 3 observations read from the data set WORK.OUTFMTN.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.OUTFMTN has 3 observations and 3 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


788
789  proc format library=work cntlin=work.outfmtn;
ERROR: For format NUM, this range is repeated, or values overlap: .-..
790  quit;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 3 observations read from the data set WORK.OUTFMTN.
NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

791


792  data want;
793  set have;
794  numeric_test = input(test,num.);
                               ----
                               48
ERROR 48-59: The informat NUM was not found or could not be loaded.

795  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and
         2 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Attempt2 : it converting to character variable also creating  extra text for not assigned number ( Example : for test ='MG' numeric_test = 'M'. so I can not use 'input' to create numeric.)

data work.outfmtn(keep=start label fmtname); 
    set work.format_from(rename=(test= start  testn=  label));  
    fmtname='$Num';
	type = 'N';
run;

proc sort data=outfmtn nodupkey;
    by fmtname start;
run;

proc format library=work cntlin=work.outfmtn;
quit;

data want;
set have;
numeric_test = put(test,$num.);
run;

How I want: ( This is an example, I have like 250 tests associated with numeric)

Thank you for your time, suggestions and inputs.

[Kurt_Bremser]

You try to create a numeric format, but all your start values are character. A numeric format needs numeric values in start, character values there result in missing numeric values.

[SASuserlot]

"Testn" variable in "format_from"is numeric. Please correct me if I misunderstood your statement. I am simply trying to create the numeric format from "format_from" using "testn" varaible, and apply that numeric format wherever I need.

[Tom · Accepted solution]

---

@SASuserlot wrote:

"Testn" variable in "format_from"is numeric. Please correct me if I misunderstood your statement. I am simply trying to create the numeric format from "format_from" using "testn" varaible, and apply that numeric format wherever I need.

---

Your PROC FORMAT code is trying to create a NUMERIC FORMAT.

Your later code is trying to use a NUMERIC INFORMAT.

Formats convert values to text.  Informats convert text to values.

If you want to convert strings to numbers you have to define an INFORMAT, not a FORMAT.  

Set the TYPE variable in your CNTLIN dataset to I (for informat).  And don't DROP the TYPE variable from the CNTLIN dataset when writing it!

data format_from;
  input test :$3. testn;
cards;
ALT 1
UR 2
KG 3
;

data have;
  input test :$3.;
cards;
ALT
UR
KG
MG
;

data work.outfmtn;
  fmtname='MYINFORMAT';
  type = 'I';
  set work.format_from;
  rename test=start testn=label;  
run;

proc sort data=outfmtn nodupkey;
  by fmtname start;
run;

proc format library=work cntlin=work.outfmtn fmtlib;
run;

[SASuserlot]

Thank you, @Tom Tom. It worked for my requirements.

Thanks to @Kurt_Bremser @ballardw  for your suggestions and solutions. 

I appreciate all of you guys.

[Kurt_Bremser]

With a numeric format, START has to be numeric. For creating an informat, LABEL has to be numeric, but in this case, you must also set TYPE to "I" in the CNTLIN dataset and keep it for PROC FORMAT.

[ballardw]

This to get the START correct for a numeric format:

data work.outfmtn(keep=start label fmtname); 
    set work.format_from(rename=(testn= start  test=  label));  
    fmtname='Num';
	type = 'N';
run;