topic Re: Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12" in SAS Procedures

Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12"

jwillis — Wed, 12 Nov 2014 21:02:56 GMT

I have attached my code and the log. I am trying to build formats from a CNTLIN dataset just to change a character value based on a numeric id value. I want to be able to change the values automatically every time the job runs and I thought a format would be the best method.

Re: Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12"

Reeza — Wed, 12 Nov 2014 21:59:31 GMT

What's the type of the variable ID?

Also, you haven't included an other in your proc format so the value passed through is the same 72 which doesn't map to anything.

Re: Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12"

Kurt_Bremser — Thu, 13 Nov 2014 07:09:00 GMT

You get the star because chekid2 has been assigned the format chgid. and that format does not cover the value 72.

Also variable chekid has been declared numeric (by length and format statement) and is used to assign a character value (the result of the put(id,chgid.)), which leads to an implicit type conversion that fails when the put yields the star.

Note the 'NOTE: Character values have been converted to numeric values at'; in a clean program, this is not acceptable.

Re: Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12"

jwillis — Mon, 17 Nov 2014 15:40:09 GMT

The variable, ID, is a numeric variable. When I encounter a matching ID value (numeric value representing the country ID in the MAPS.WORLD SAS dataset) in my dataset that has a matching country ID, I want to change the character variable, RISKA, to a character value of "1","2","3","4" or "5". The ID numeric value of 72 comes from the MAPS.WORLD dataset. I do not understand where the value of "*" is being read from or created from. My attempts to add an "OTHER" line to the numeric format, have not worked.

Re: Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12"

jwillis — Mon, 17 Nov 2014 15:41:28 GMT

"Note the 'NOTE: Character values have been converted to numeric values at'; in a clean program, this is not acceptable." Correct, that is why I am asking for help.

Re: Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12"

Tom — Mon, 17 Nov 2014 15:54:29 GMT

Your HLO variable should be character and you should set it to the letter O when you are defining the value for the OTHER category.

Re: Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12"

RW9 — Mon, 17 Nov 2014 16:03:27 GMT

Looking at the format statement:

START, END‚ LABEL

., .‚ 0

399‚ 399‚ 1

573‚ 573‚ 1

There is no result for 72. Hence the format returns a "*", i.e. the statement chekid = put(id,chgid.);, is trying to assign an asterix "*" which means no value found, into a numeric variable which cannot handle the non-numeric data. In your format statements put an Other = -99 (or some value) so that values which do not fir into your windows return a numeric value.

Re: Why am I receiving the note: "NOTE: Invalid numeric data, '*' , at line 2446 column 12"

jwillis — Mon, 17 Nov 2014 17:26:44 GMT

I had a combination of issues in the data step that created the cntlin datasets. The code below solved my problems.

proc sql; drop table WORK.changethese3; quit;
DATA work.CHANGETHESE3(KEEP=FMTNAME START END LABEL min max hlo) ;
    LENGTH FMTNAME $ 8 START 5 LABEL 8;
retain type "N" FMTNAME "chgid" min 3 max 8;
    set work.changethese2 END=LASTREC;
if _n_ = 1 then do;
     START=0;
     END=(ID-1);
        LABEL=0;
OUTPUT;
end;
    START=ID;
END=ID;
    LABEL=1;
    OUTPUT;
     IF LASTREC THEN DO;
        LABEL=0;
     START=(ID + 1); END=999;
     hlo = '0';
        OUTPUT;
   END;
run;

PROC FORMAT CNTLIN=WORK.changethese3;
QUIT;

proc format fmtlib;
select chgid;
QUIT;

proc sql; drop table WORK.changethese4; quit;
DATA work.CHANGETHESE4(KEEP=FMTNAME START END LABEL hlo min max OTHER) ;
    LENGTH FMTNAME $ 8 START 5 LABEL $1;
    set work.changethese2 END=LASTREC;
     retain FMTNAME "chgrsk" min 1 max 5;
   OTHER = ' ';
    START = ID;
END = ID;
    LABEL = riska;
    OUTPUT;
    IF LASTREC THEN DO;
      START=.;
   END = .;
      LABEL=' ';
   hlo = '0';
      OUTPUT;
      END;
run;

PROC FORMAT CNTLIN=WORK.changethese4;
run;

proc format fmtlib;
select chgrsk;
run;

1596 data work.world;    /* unprojected */
1597         length chekid 8 chekrisk $1;
1598         format chekid 5. chekrisk $1. chekid2 chgid.;
1599     set work.worldx;
1600    chekid = put(id,chgid.);
1601    chekid2 = id;
1602     if chekid = 1 then do;
1603                chekrisk = put(id,chgrsk.);
1604               riska=put(id,chgrsk.);
1605               put id= riska=;
1606   end;
1607    if _n_ < 5 then do;   put "first four rows" id= chekid= chekid2= chekrisk =; end;
1608    drop chekid chekrisk chekid2;
1609 run;

NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
1600:12
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
ID=399 riska=2
ID=573 riska=1
ID=573 riska=1
ID=573 riska=1
NOTE: There were 45805 observations read from the data set WORK.WORLDX.
NOTE: The data set WORK.WORLD has 45805 observations and 9 variables.