hello dear SAS experts,
I used the following Code to define an informat:
/* Monatsformate für Erfolgsplan */ data monatefp; length start $2 label 3; input start label; datalines; A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10 K 11 L 12 M 21 N 22 O 23 P 24 Q 25 R 26 S 27 T 28 U 29 V 30 W 31 X 32 Y 33 ; run; data monatefpf; set monatefp end=last; retain fmtname 'Monatefpf' type 'J'; output; run; proc format cntlin=Monatefpf fmtlib; run;
this Code produced the following informat:
---------------------------------------------------------------------------- | FORMAT NAME: ERFGPLNF LENGTH: 2 NUMBER OF VALUES: 1 | | MIN LENGTH: 1 MAX LENGTH: 40 DEFAULT LENGTH: 2 FUZZ: STD | |--------------------------------------------------------------------------| |START |END |LABEL (VER. V7|V8 19MAR2021:17:30:29)| |----------------+----------------+----------------------------------------| | .| .|33 | ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- | INFORMAT NAME: @$ERFGPLNF LENGTH: 2 | | MIN LENGTH: 1 MAX LENGTH: 40 DEFAULT LENGTH: 2 FUZZ: 0 | |--------------------------------------------------------------------------| |START |END |INVALUE(VER. 9.4 19MAR2021:17:52:28)| |----------------+----------------+----------------------------------------| |A |A |1 | |B |B |2 | |C |C |3 | |D |D |4 | |E |E |5 | |F |F |6 | |G |G |7 | |H |H |8 | |I |I |9 | |J |J |10 | |K |K |11 | |L |L |12 | |M |M |21 | |N |N |22 | |O |O |23 | |P |P |24 | |Q |Q |25 | |R |R |26 | |S |S |27 | |T |T |28 | |U |U |29 | |V |V |30 | |W |W |31 | |X |X |32 | |Y |Y |33 | ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- | INFORMAT NAME: @$MONATEFPF LENGTH: 2 | | MIN LENGTH: 1 MAX LENGTH: 40 DEFAULT LENGTH: 2 FUZZ: 0 | |--------------------------------------------------------------------------| |START |END |INVALUE(VER. 9.4 19MAR2021:17:57:18)| |----------------+----------------+----------------------------------------| |A |A |1 | |B |B |2 | |C |C |3 | |D |D |4 | |E |E |5 | |F |F |6 | |G |G |7 | -----------------+----------------+----------------------------------------- | INFORMAT NAME: @$MONATEFPF LENGTH: 2 | | MIN LENGTH: 1 MAX LENGTH: 40 DEFAULT LENGTH: 2 FUZZ: 0 | |--------------------------------------------------------------------------| |START |END |INVALUE (CONT'D)| |----------------+----------------+----------------------------------------| |H |H |8 | |I |I |9 | |J |J |10 | |K |K |11 | |L |L |12 | |M |M |21 | |N |N |22 | |O |O |23 | |P |P |24 | |Q |Q |25 | |R |R |26 | |S |S |27 | |T |T |28 | |U |U |29 | |V |V |30 | |W |W |31 | |X |X |32 | |Y |Y |33 | ----------------------------------------------------------------------------
I Need to use it to transform the variable Monat_O into a numeric Variable and I tried:
data Erfolgsplan_AG_Gesamt /*(drop=Monat_O)*/;
set Daten4Dashboard1 (keep=Monat Monat_O);
*if stufe in ('AG_FIL', 'AG_GESAMT', 'Mitte/Ost') then MARKTREGION_BT = Stufe;
*if stufe in ('AG_FIL', 'AG_GESAMT', 'Mitte/Ost') then NLBEZ_BT = Stufe;
Monat_test1=input(Monat_O, $Monatefpf.);
Monat_test2=input(Monat_O, Monatefpf.);
run;
the result is:
I have 2 Problems:
- I Need the Monat_test1 or Monat_test2 to be NUMERIC
- I see some Errors using the informat (L => L instead of 12 for instance)
How can I solve this?
Regards
Look up the meaning of the TYPE variable in the CNTLIN dataset.
J -> Character informat.
I -> Numeric informat.
Changing it to I, and using it without the $ is important because you've defined both types by now. Or makes sure to test it in a brand new session.
This works as expected.
data monatefp;
length start $2 label 3;
input start label;
datalines;
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J 10
K 11
L 12
M 21
N 22
O 23
P 24
Q 25
R 26
S 27
T 28
U 29
V 30
W 31
X 32
Y 33
;
run;
data monatefpf;
set monatefp end=last;
retain fmtname 'testFormat' type 'I';
output;
run;
proc format cntlin=Monatefpf ; run;
data want /*(drop=Monat_O)*/;
set monatefp ;
Monat_test1 = input(start, testFormat.);
run;
Look up the meaning of the TYPE variable in the CNTLIN dataset.
J -> Character informat.
I -> Numeric informat.
Hello @PierreYvesILY,
The two values in column Monat_O which were left unchanged by your original character informat, 'J' and 'L', suggest that in those observations variable Monat_O (length >1, right?) contains more than just these single letters: possibly invisible characters. In the case of trailing additional characters the problem should vanish after changing the type of the informat to numeric (type='I') because the default length is then 1. With leading invisible characters, however, the numeric informat would produce missing numeric values.
I would identify ($HEXw. format) and then remove (e.g., COMPRESS function) the unexpected characters from variable Monat_O in any case.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.