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.
... View more
