topic Re: Invalid numeric data in SAS Procedures

Invalid numeric data

xxBingoBongoxx — Thu, 26 Nov 2020 20:49:26 GMT

Hi, I'm new with SAS UE.
I don't understand why i'm getting this "NOTE: Invalid numeric data" thus wrong dataset. I think i provide the format proc a correct numeric type

The code:

proc fcmp outlib=work.funcs.ConvFuncs;
	function toWatt(x);
		return(x*735.5);
	endsub;
run;

option cmplib = work.funcs;

proc format;
	picture w
		low-high='00000,00 Watt' (decsep=',');
run;

data cars(keep = make origin type horsepower);
	set sashelp.cars;
run;

proc sort data=cars out=sortcars;
	by make;
run;

data max_power_by_types_cars (keep = make suv sedan wagon truck hybrid sports a);
	array max_power {6} _temporary_;
	array types {6} suv sedan wagon truck hybrid sports;
	set sortcars(keep = make type horsepower);
	by make;
	if first.make then do i = 1 to 6; max_power{i}=0; types{i}=0; end;
	select(type);
		when('SUV') i=1;
		when('Sedan') i=2;
		when('Wagon') i=3;
		when('Truck') i=4;
		when('Hybrid') i=5;
		when('Sports') i=6;
		otherwise i=0;
	end;
	if(i>0) then do; 
		if (max_power{i} < horsepower) then max_power{i} = horsepower; *put max_power{i}=  horsepower= make= type=;
	end;
	if last.make then do;
		do i = 1 to 6; if (max_power{i} > 0) then types{i}=put(toWatt(max_power{i}), w.); end;
		output;
	end;
	else return;
run;

Piece of LOGS:

125 data max_power_by_types_cars (keep = make suv sedan wagon truck hybrid sports);
126 array max_power {6} _temporary_;
127 array types {6} suv sedan wagon truck hybrid sports;
128 set sortcars(keep = make type horsepower);
129 by make;
130 if first.make then do i = 1 to 6; max_power{i}=0; types{i}=0; end;
131 select(type);
132 when('SUV') i=1;
133 when('Sedan') i=2;
134 when('Wagon') i=3;
135 when('Truck') i=4;
136 when('Hybrid') i=5;
137 when('Sports') i=6;
138 otherwise i=0;
139 end;
140 if(i>0) then do;
141 if (max_power{i} < horsepower) then max_power{i} = horsepower; *put max_power{i}= horsepower= make= type=;
142 end;
143 if last.make then do;
144 do i = 1 to 6; if (max_power{i} > 0) then types{i}=put(toWatt(max_power{i}), w.); end;
145 output;
146 end;
147 else return;
148 run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
144:45
NOTE: Invalid numeric data, '94907,50 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '98585,00 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '13295,00 Watt' , at line 144 column 54.
suv=. sedan=. wagon=. truck=. hybrid=. sports=. Make=Acura Type=Sports Horsepower=290 FIRST.Make=0 LAST.Make=1 i=7 _ERROR_=1 _N_=7
NOTE: Invalid numeric data, '50070,00 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '50070,00 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '30975,00 Watt' , at line 144 column 54.
suv=. sedan=. wagon=. truck=. hybrid=. sports=. Make=Audi Type=Wagon Horsepower=340 FIRST.Make=0 LAST.Make=1 i=7 _ERROR_=1 _N_=26
NOTE: Invalid numeric data, '39037,50 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '39037,50 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '35332,00 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '44921,50 Watt' , at line 144 column 54.
suv=. sedan=. wagon=. truck=. hybrid=. sports=. Make=BMW Type=Wagon Horsepower=184 FIRST.Make=0 LAST.Make=1 i=7 _ERROR_=1 _N_=46
NOTE: Invalid numeric data, ' 2262,50 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '76520,00 Watt' , at line 144 column 54.
suv=. sedan=. wagon=. truck=. hybrid=. sports=. Make=Buick Type=Sedan Horsepower=240 FIRST.Make=0 LAST.Make=1 i=7 _ERROR_=1 _N_=55
NOTE: Invalid numeric data, '35360,00 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '20650,00 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '53747,50 Watt' , at line 144 column 54.
NOTE: Invalid numeric data, '35360,00 Watt' , at line 144 column 54.

Re: Invalid numeric data

ballardw — Thu, 26 Nov 2020 20:53:02 GMT

@xxBingoBongoxx wrote:

Hi, I'm new with SAS UE.
I don't understand why i'm getting this "NOTE: Invalid numeric data" thus wrong dataset. I think i provide the format proc a correct numeric type

The code:
proc fcmp outlib=work.funcs.ConvFuncs;
	function toWatt(x);
		return(x*735.5);
	endsub;
run;

option cmplib = work.funcs;

proc format;
	picture w
		low-high='00000,00 Watt' (decsep=',');
run;

data cars(keep = make origin type horsepower);
	set sashelp.cars;
run;

proc sort data=cars out=sortcars;
	by make;
run;

data max_power_by_types_cars (keep = make suv sedan wagon truck hybrid sports a);
	array max_power {6} _temporary_;
	array types {6} suv sedan wagon truck hybrid sports;
	set sortcars(keep = make type horsepower);
	by make;
	if first.make then do i = 1 to 6; max_power{i}=0; types{i}=0; end;
	select(type);
		when('SUV') i=1;
		when('Sedan') i=2;
		when('Wagon') i=3;
		when('Truck') i=4;
		when('Hybrid') i=5;
		when('Sports') i=6;
		otherwise i=0;
	end;
	if(i>0) then do; 
		if (max_power{i} < horsepower) then max_power{i} = horsepower; *put max_power{i}=  horsepower= make= type=;
	end;
	if last.make then do;
		do i = 1 to 6; if (max_power{i} > 0) then types{i}=put(toWatt(max_power{i}), w.); end;
		output;
	end;
	else return;
run;
Piece of LOGS:
125 data max_power_by_types_cars (keep = make suv sedan wagon truck hybrid sports);

126 array max_power {6} _temporary_;

127 array types {6} suv sedan wagon truck hybrid sports;

128 set sortcars(keep = make type horsepower);

129 by make;

130 if first.make then do i = 1 to 6; max_power{i}=0; types{i}=0; end;

131 select(type);

132 when('SUV') i=1;

133 when('Sedan') i=2;

134 when('Wagon') i=3;

135 when('Truck') i=4;

136 when('Hybrid') i=5;

137 when('Sports') i=6;

138 otherwise i=0;

139 end;

140 if(i>0) then do;

141 if (max_power{i} < horsepower) then max_power{i} = horsepower; *put max_power{i}= horsepower= make= type=;

142 end;

143 if last.make then do;

144 do i = 1 to 6; if (max_power{i} > 0) then types{i}=put(toWatt(max_power{i}), w.); end;

145 output;

146 end;

147 else return;

148 run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

144:45

NOTE: Invalid numeric data, '94907,50 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '98585,00 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '13295,00 Watt' , at line 144 column 54.

suv=. sedan=. wagon=. truck=. hybrid=. sports=. Make=Acura Type=Sports Horsepower=290 FIRST.Make=0 LAST.Make=1 i=7 _ERROR_=1 _N_=7

NOTE: Invalid numeric data, '50070,00 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '50070,00 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '30975,00 Watt' , at line 144 column 54.

suv=. sedan=. wagon=. truck=. hybrid=. sports=. Make=Audi Type=Wagon Horsepower=340 FIRST.Make=0 LAST.Make=1 i=7 _ERROR_=1 _N_=26

NOTE: Invalid numeric data, '39037,50 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '39037,50 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '35332,00 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '44921,50 Watt' , at line 144 column 54.

suv=. sedan=. wagon=. truck=. hybrid=. sports=. Make=BMW Type=Wagon Horsepower=184 FIRST.Make=0 LAST.Make=1 i=7 _ERROR_=1 _N_=46

NOTE: Invalid numeric data, ' 2262,50 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '76520,00 Watt' , at line 144 column 54.

suv=. sedan=. wagon=. truck=. hybrid=. sports=. Make=Buick Type=Sedan Horsepower=240 FIRST.Make=0 LAST.Make=1 i=7 _ERROR_=1 _N_=55

NOTE: Invalid numeric data, '35360,00 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '20650,00 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '53747,50 Watt' , at line 144 column 54.

NOTE: Invalid numeric data, '35360,00 Watt' , at line 144 column 54.

The array Types contains numeric values as you recognize withe the assignment block in the Select/end.

On line 144 you are using :

 types{i}=put(toWatt(max_power{i}), w.);

The PUT function creates a character value. So attempting to assign something like '20650,00 Watt' to a numeric value is incorrect.

Re: Invalid numeric data

Reeza — Thu, 26 Nov 2020 21:05:24 GMT

Do not use a PUT() statement to convert your data at all. Leave it numeric and the format controls the appearance of the variable. If you want to store it with the decimal and Watt as a variable, it will have to be a character variable. If you have a character variable it's a pain in the a** to sort in any reasonable fashion though or group.

Re: Invalid numeric data

xxBingoBongoxx — Thu, 26 Nov 2020 21:08:37 GMT

So how do i make types array of type string in this case to make it work ?

Re: Invalid numeric data

xxBingoBongoxx — Thu, 26 Nov 2020 21:10:37 GMT

the thing is i don't care how to store it, i just need to make an output txt from this in the end

Re: Invalid numeric data

Reeza — Thu, 26 Nov 2020 21:14:54 GMT

It will export fine with the format applied. Have you tried it and it didn't work?

Re: Invalid numeric data

Reeza — Thu, 26 Nov 2020 21:15:35 GMT

I mean you can always do the extra work if you want to but it's like putting a wrapped gift in a gift bag.

Re: Invalid numeric data

ballardw — Thu, 26 Nov 2020 21:34:09 GMT

@xxBingoBongoxx wrote:
So how do i make types array of type string in this case to make it work ?

Do not make the max_power array temporary. Use those values in reports with the format applied.

Or make a third array to store the results in using the Put.

I think that you may want to check on your formatted values with some known horsepower and expected results. A quick search shows the typical horsepower to watt conversion is 1HP= 745.7watts.

Re: Invalid numeric data

Tom — Thu, 26 Nov 2020 22:03:33 GMT

The program seems way too complicated. Why not just use PROC SUMMARY to find the MAX?

%let factor=735.5 ;

proc format ;
 value $types
    'SUV'   ='1 SUV'
    'Sedan' ='2 Sedan'
    'Wagon' ='3 Wagon'
    'Truck' ='4 Truck'
    'Hybrid'='5 Hybrid'
    'Sports'='6 Sports'
    other   ='0 Other'
 ;
run;

proc summary data=sashelp.cars nway ;
  class make type ;
  var horsepower ;
  format type $types. ;
  output out=max_power_by_types_cars max=maxpower ;
run;

data max_power_by_types_cars;
  set max_power_by_types_cars;
  watts=maxpower*&factor ;
run;

proc report data=max_power_by_types_cars ;
  column make type,(maxpower watts);
  define make / group;
  define type / across format=$types. ;
  format watts comma9.1 ;
run;