Solved: Re: error with format catalogue

acordes · Posted 09-13-2022 09:53 AM

@BrunoMueller I have a strange behaviour with a fmt + vvalue code.

I use it quite often instead of joining, and normally it does what I want.

But I have a bug and I cannot fix it.

The first code block works just fine and applies the format.

But the second block does nothing. I have checked that both informat tables have the same column formats, I have checked for values that exist and should be formatted.

I don't know what's wrong.

Data to lookup:

data WORK.OKS2;
  infile datalines dsd truncover;
  input _RMCTMAIMG:$6.;
datalines4;
1213A1
1214A1
1214A3
1214D1
1214D3
1222A1
1223A1
1223D1
1224A1
1224D1
1224DW
1224H1
137263
13726X
137272
137282
1372G2
1372Q2
1379V3
13821X
13821Y
13822Y
1382HX
1382NX
1382NY
1382TY
1389TX
1389TY
138RW3
16222V
1623Z5
162421
162425
162473
16325V
1632KX
16345V
16345Z
190100
190201
190300
190304
190800
190802
191201
191300
1F7312
1F7313
1F7363
1F73P2
1F847X
1F847Y
1J1234
1J16N4
1K1024
1K102A
1K10E1
1K10G2
1K116C
1K122A
1K122C
1K1262
1K126B
1K126C
1K12E1
1K1B2A
1K1B6B
1K1BG2
1K222A
1K526B
1M1324
1M13KC
1M13V4
1M1546
1M15KC
1M1766
1P1112
1P1126
1P11D2
1P11E2
1P11G2
1P11H1
1P11T1
1P11U2
1P11Z2
1P1212
1P1226
1P1231
1P1233
1P12D2
1P12H1
1P12U2
1P12U5
1P13H1
1P1561
1P1591
1P15H1
1P17T1
1P17U2
1P17Z2
;;;;

The format table that WORKS:

data WORK.FMT_SUB;
  infile datalines dsd truncover;
  input start:$6. label:$200. fmtname:$10. type:$1. HLO:$1.;
datalines4;
1M1766,SEAT|Leon|LEON,TMAIMG_MAP,c,
1M15KC,SEAT|Leon|LEON,TMAIMG_MAP,c,
1M1546,SEAT|Leon|LEON,TMAIMG_MAP,c,
1M13V4,SEAT|Leon|LEON,TMAIMG_MAP,c,
1M13KC,SEAT|Leon|LEON,TMAIMG_MAP,c,
1M1324,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P17Z2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P17U2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P17T1,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P15H1,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P1591,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P1561,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P13H1,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P12U5,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P12U2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P12H1,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P12D2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P1233,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P1231,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P1226,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P1212,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P11Z2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P11U2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P11T1,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P11H1,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P11G2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P11E2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P11D2,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P1126,SEAT|Leon|LEON,TMAIMG_MAP,c,
1P1112,SEAT|Leon|LEON,TMAIMG_MAP,c,
1382TY,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1382NY,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1214D3,Volkswagen|Up|UP,TMAIMG_MAP,c,
1224H1,Volkswagen|Up|UP,TMAIMG_MAP,c,
1382NX,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1213A1,Volkswagen|Up|UP,TMAIMG_MAP,c,
1382HX,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1224DW,Volkswagen|Up|UP,TMAIMG_MAP,c,
1214D1,Volkswagen|Up|UP,TMAIMG_MAP,c,
13822Y,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
13821Y,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
13821X,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1224D1,Volkswagen|Up|UP,TMAIMG_MAP,c,
1F847Y,Volkswagen|Eos|EOS,TMAIMG_MAP,c,
1F847X,Volkswagen|Eos|EOS,TMAIMG_MAP,c,
1K526B,Volkswagen|Golf Variant|GOLF,TMAIMG_MAP,c,
16345Z,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
1379V3,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1F73P2,Volkswagen|Eos|EOS,TMAIMG_MAP,c,
16345V,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
1214A3,Volkswagen|Up|UP,TMAIMG_MAP,c,
1F7363,Volkswagen|Eos|EOS,TMAIMG_MAP,c,
1F7313,Volkswagen|Eos|EOS,TMAIMG_MAP,c,
1F7312,Volkswagen|Eos|EOS,TMAIMG_MAP,c,
1K222A,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
1632KX,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
16325V,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
1J16N4,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1224A1,Volkswagen|Up|UP,TMAIMG_MAP,c,
1K1BG2,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1372Q2,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1214A1,Volkswagen|Up|UP,TMAIMG_MAP,c,
1K1B6B,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1372G2,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1K1B2A,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1K12E1,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1K126C,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1K126B,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1K1262,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1K122C,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1K122A,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
162473,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
1223D1,Volkswagen|Up|UP,TMAIMG_MAP,c,
162425,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
1K116C,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
162421,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
137282,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1623Z5,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
1K10G2,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1K10E1,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
137272,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1K102A,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
1K1024,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
13726X,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1J1234,Volkswagen|Golf|GOLF,TMAIMG_MAP,c,
16222V,Volkswagen|Jetta|JETTA,TMAIMG_MAP,c,
1223A1,Volkswagen|Up|UP,TMAIMG_MAP,c,
137263,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
138RW3,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1389TY,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
1222A1,Volkswagen|Up|UP,TMAIMG_MAP,c,
1389TX,Volkswagen|Scirocco|SCIROCCO,TMAIMG_MAP,c,
190304,Ducati|Monster|MONSTER,TMAIMG_MAP,c,
190300,Ducati|Monster|MONSTER,TMAIMG_MAP,c,
190201,Ducati|Monster|MONSTER,TMAIMG_MAP,c,
190100,Ducati|Monster|MONSTER,TMAIMG_MAP,c,
191300,Ducati|Monster|MONSTER,TMAIMG_MAP,c,
191201,Ducati|Monster|MONSTER,TMAIMG_MAP,c,
190802,Ducati|Monster|MONSTER,TMAIMG_MAP,c,
190800,Ducati|Monster|MONSTER,TMAIMG_MAP,c,
;;;;

Using the following syntax:

proc format library=work cntlin=fmt_sub;
run;

data oks_fmt;
set oks2;
format g1  $TMAIMG_MAP.;
g1=_rmctmaimg;
_brand=scan(vvalue(g1), 1, "|");
run;

But what fails to get formatted is the following:

data WORK.FMT_FAILED;
  infile datalines dsd truncover;
  input start:$6. label:$200. fmtname:$10. type:$1. HLO:$1.;
datalines4;
1213A1,|165,RV_GROUP,c,
1214A1,|165,RV_GROUP,c,
1214A3,|535,RV_GROUP,c,
1214D1,|165,RV_GROUP,c,
1214D3,|535,RV_GROUP,c,
1222A1,|535,RV_GROUP,c,
1223A1,|165,RV_GROUP,c,
1223D1,|165,RV_GROUP,c,
1224A1,|165,RV_GROUP,c,
1224D1,|165,RV_GROUP,c,
1224DW,|165,RV_GROUP,c,
1224H1,|165,RV_GROUP,c,
137263,|159,RV_GROUP,c,
13726X,|159,RV_GROUP,c,
137272,|159,RV_GROUP,c,
137282,|159,RV_GROUP,c,
1372G2,|514,RV_GROUP,c,
1372Q2,|514,RV_GROUP,c,
1379V3,|514,RV_GROUP,c,
13821X,|515,RV_GROUP,c,
13821Y,|515,RV_GROUP,c,
13822Y,|515,RV_GROUP,c,
1382HX,|514,RV_GROUP,c,
1382NX,|514,RV_GROUP,c,
1382NY,|514,RV_GROUP,c,
1382TY,|514,RV_GROUP,c,
1389TX,|514,RV_GROUP,c,
1389TY,|514,RV_GROUP,c,
138RW3,|514,RV_GROUP,c,
16222V,|520,RV_GROUP,c,
1623Z5,|519,RV_GROUP,c,
162421,|520,RV_GROUP,c,
162425,|520,RV_GROUP,c,
162473,|520,RV_GROUP,c,
16325V,|520,RV_GROUP,c,
1632KX,|519,RV_GROUP,c,
16345V,|520,RV_GROUP,c,
16345Z,|520,RV_GROUP,c,
190100,|D01,RV_GROUP,c,
190201,|D01,RV_GROUP,c,
190300,|D01,RV_GROUP,c,
190304,|D01,RV_GROUP,c,
190800,|D01,RV_GROUP,c,
190802,|D01,RV_GROUP,c,
191201,|D01,RV_GROUP,c,
191300,|D01,RV_GROUP,c,
1F7312,|143,RV_GROUP,c,
1F7313,|143,RV_GROUP,c,
1F7363,|143,RV_GROUP,c,
1F73P2,|W03,RV_GROUP,c,
1F847X,|143,RV_GROUP,c,
1F847Y,|143,RV_GROUP,c,
1J1234,|W03,RV_GROUP,c,
1J16N4,|W03,RV_GROUP,c,
1K1024,|W01,RV_GROUP,c,
1K102A,|W01,RV_GROUP,c,
1K10E1,|W03,RV_GROUP,c,
1K10G2,|W03,RV_GROUP,c,
1K116C,|W01,RV_GROUP,c,
1K122A,|W01,RV_GROUP,c,
1K122C,|W03,RV_GROUP,c,
1K1262,|W01,RV_GROUP,c,
1K126B,|W01,RV_GROUP,c,
1K126C,|W01,RV_GROUP,c,
1K12E1,|W03,RV_GROUP,c,
1K1B2A,|W01,RV_GROUP,c,
1K1B6B,|W01,RV_GROUP,c,
1K1BG2,|W03,RV_GROUP,c,
1K222A,|520,RV_GROUP,c,
1K526B,|W01,RV_GROUP,c,
1M1324,|638,RV_GROUP,c,
1M13KC,|344,RV_GROUP,c,
1M13V4,|638,RV_GROUP,c,
1M1546,|638,RV_GROUP,c,
1M15KC,|344,RV_GROUP,c,
1M1766,|638,RV_GROUP,c,
1P1112,|638,RV_GROUP,c,
1P1126,|638,RV_GROUP,c,
1P11D2,|344,RV_GROUP,c,
1P11E2,|344,RV_GROUP,c,
1P11G2,|638,RV_GROUP,c,
1P11H1,|344,RV_GROUP,c,
1P11T1,|344,RV_GROUP,c,
1P11U2,|638,RV_GROUP,c,
1P11Z2,|638,RV_GROUP,c,
1P1212,|638,RV_GROUP,c,
1P1226,|638,RV_GROUP,c,
1P1231,|638,RV_GROUP,c,
1P1233,|638,RV_GROUP,c,
1P12D2,|344,RV_GROUP,c,
1P12H1,|344,RV_GROUP,c,
1P12U2,|638,RV_GROUP,c,
1P12U5,|638,RV_GROUP,c,
1P13H1,|344,RV_GROUP,c,
1P1561,|638,RV_GROUP,c,
1P1591,|344,RV_GROUP,c,
1P15H1,|344,RV_GROUP,c,
1P17T1,|344,RV_GROUP,c,
1P17U2,|638,RV_GROUP,c,
1P17Z2,|638,RV_GROUP,c,
;;;;

proc format library=work cntlin=fmt_failed;
run;

data no_oks_fmt;
set oks2;
format g1  $RV_GROUP.;
g1=_rmctmaimg;
grupo=vvalue(g1);
run;

I don't get why the second format does not resolve, it seems absolutely identical to me.

FreelanceReinh · Posted 09-13-2022 10:20 AM

Hello @acordes,

Move the FORMAT statement after the assignment statement for variable g1. Otherwise the format length (4) determines the length of g1, which means truncation.

View solution in original post

acordes · Posted 09-13-2022 10:15 AM

If I change the label variable making it longer it works...

strange.

DATA FMT_FAILED;
SET FMT_FAILED(RENAME=('LABEL'N=LBL));
'LABEL'N=CATS("Why it isn't working?", LBL);
fmtname='trying';
DROP LBL;
RUN;


proc format library=work cntlin=fmt_failed;
run;


data oks_fmt;
set oks2;
format g1  $trying.;
g1=_rmctmaimg;
grupo=scan(vvalue(g1), 2, "|");
run;

FreelanceReinh · Posted 09-13-2022 10:20 AM

Hello @acordes,

Move the FORMAT statement after the assignment statement for variable g1. Otherwise the format length (4) determines the length of g1, which means truncation.

ballardw · Posted 09-13-2022 10:23 AM

See what happens with this:

data no_oks_fmt;
   set oks2;
   length g1 $ 6;
   format g1  $RV_GROUP.;
   g1=_rmctmaimg;
   grupo=vvalue(g1);
run;

If you look at the result from your code you may see that G1 was set to a length of 4, so when you assigned a value to it from _Rmctmaimg it was truncated to 4 characters. So since the entire value was not present in G1 the format had insufficient information to display the values based on the 6 character definitions.

Hint: Proc Contents is your friend.

acordes · Posted 09-13-2022 11:06 AM

Ok, understood.

But what makes g1 length 4???

I suppose it's the target of the formatting, the label variable from the format table. this is length 4.

FreelanceReinh · Posted 09-13-2022 11:11 AM

@acordes wrote:

But what makes g1 length 4???

In your data step, variable g1 was first mentioned in the FORMAT statement assigning a character format of length 4 to it. Thus it was defined as a character variable of length 4 during the compilation phase of the step and no subsequent statement could change that.

Tom · Posted 09-13-2022 11:13 AM

@acordes wrote:

Ok, understood.

But what makes g1 length 4???

I suppose it's the target of the formatting, the label variable from the format table. this is length 4.

If you do not define your variables then SAS is forced to GUESS how to define them when it first encounters them. If the first place is a FORMAT statement then SAS will use the TYPE of the format (numeric or character) to define the TYPE of the variable. In the case of a character variable (character format) it will also need to GUESS how long the variable should be. In that case it will base it on the display width of the format. That is not necessarily going to be a good choice, but what choice does it have when you have skipped defining the variable for it?

BrunoMueller · Posted 09-13-2022 10:57 AM

I suggest to add a default length to the format. The variable is named DEAULT.

The 2025 SAS Hackathon has begun!

SAS Training: Just a Click Away