Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 217
Accepted Solution

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

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.

Attachment

Accepted Solutions
Solution
‎11-17-2014 12:26 PM
Regular Contributor
Posts: 217

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

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)Smiley SadColumn).
      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.

View solution in original post


All Replies
Super User
Posts: 17,960

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

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.

Regular Contributor
Posts: 217

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

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.

Super User
Posts: 6,972

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 217

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

"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.

Super User
Super User
Posts: 7,430

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

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.

Super User
Super User
Posts: 6,502

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

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.

Solution
‎11-17-2014 12:26 PM
Regular Contributor
Posts: 217

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

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)Smiley SadColumn).
      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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 357 views
  • 4 likes
  • 5 in conversation