Data step tries to convert character data to numeric and then fails.

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Data step tries to convert character data to numeric and then fails.

I am writing what should be a fairly simple program generator which is intended to read a spread sheet of validation checks I want to run, create the macro calls to actually run the checks, and then output the macro call strings into a sas file and run them. The back end of the program is working fine, reading the macro call strings from the dataset created in the first step, putting them to a .sas file, then including the file to run them. 

 

The problem comes from the first datastep which is compiling the macro call, specificall the part that puts together the variable string for each call. The macro to do so is -

%macro buildparameters(location=);
proc import datafile="&location." out=import dbms=xlsx; run;
data parameters; 
	set import (Where=(Run = "Y")); 
	check=tranwrd(check,".","");
	check=compress(trim("%"||check));
	format call $200.;
	
	%do i=1 %to 10;
		%if var&i ne "" %then %do; 
			vars=compress(vars||var&i||",");
		%end;
	%end;
	vars="("||vars||")";
	vars=compress(tranwrd(vars,",,",""));
	if vars="(.)" then vars="";
	call=compress(check||vars||";");

run;
%mend buildparameters;

It imports the spreadsheet correctly, which contains the variables Run, Check, Var1-Var10.  It then removes decimal points if present in the check variable, compresses the field, and appends the % sign to the front of it which is the begining of the macro call.  The problem comes in when it starts to loop through the Var columns, for some reason it keeps trying to convert the columns from character to numeric, then reads the columns as invalid numeric data and fails.  The log messages appear as follows -

 

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      3:28    5:28    7:28    9:28    11:28   13:28   15:28   17:28   19:28   21:28   23:29
      23:75
NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      3:19    5:19    7:19    9:19    11:19   13:19   15:19   17:19   19:19   21:19   23:12
NOTE: Invalid numeric data, '.,' , at line 3 column 19.
NOTE: Invalid numeric data, '.,' , at line 5 column 19.
NOTE: Invalid numeric data, '.,' , at line 7 column 19.
NOTE: Invalid numeric data, '.,' , at line 9 column 19.
NOTE: Invalid numeric data, '.,' , at line 11 column 19.
NOTE: Invalid numeric data, '.,' , at line 13 column 19.
NOTE: Invalid numeric data, '.,' , at line 15 column 19.
NOTE: Invalid numeric data, '.,' , at line 17 column 19.
NOTE: Invalid numeric data, '.,' , at line 19 column 19.
NOTE: Invalid numeric data, '.,' , at line 21 column 19.
Run=Y Check=%DM10 Var1=  Var2=  Var3=  Var4=  Var5=  Var6=  Var7=  Var8=  var9=  var10=
call=%DM10(.); vars=. _ERROR_=1 _N_=1
NOTE: Invalid numeric data, '.dmdata=A,' , at line 3 column 19.
NOTE: Invalid numeric data, '.dmsubj=B,' , at line 5 column 19.
NOTE: Invalid numeric data, '.dmusubj=C,' , at line 7 column 19.
NOTE: Invalid numeric data, '.dmsite=D,' , at line 9 column 19.
NOTE: Invalid numeric data, '.dmbirth=E,' , at line 11 column 19.
NOTE: Invalid numeric data, '.dmsex=F,' , at line 13 column 19.
NOTE: Invalid numeric data, '.vsdata=G,' , at line 15 column 19.
NOTE: Invalid numeric data, '.Test=H,' , at line 17 column 19.
NOTE: Invalid numeric data, '.vsheight=I,' , at line 19 column 19.
NOTE: Invalid numeric data, '.vsweight=J,' , at line 21 column 19.

I can't seem to figure out why it's trying to force character values to numeric, the import dataset has the var1-var10 fields correctly identified as characters.


Accepted Solutions
Solution
‎08-22-2016 03:06 PM
New Contributor
Posts: 4

Re: Data step tries to convert character data to numeric and then fails.


ballardw wrote:

Because you wrap the first possible appearance of the variable VARS in that never true macro code the first time the datastep sees the variable Vars is in the line

vars="("||vars||")";

By default undefined variables default to numeric. Since VARS is therefore numeric for all steps any thing that attempts to use VARS as a result will attempt to be turned into numeric values and fail miserably with your data.

See this short example:

data example;
   vars="("||vars||")";
run;

As several of use have mentioned your use of a macro %IF is extremely suspect. It probably should be a datastep loop over an array referencing the Var1-var10.

 

Perhaps you wanted:

array v var1-var10;
do i=1 to dim(v);
   if v[i] ne "" then vars= compress(vars||v[i]||",");
end;

 

 

This is actually answer sort of. I haven't tried it changed from the %do %if loop to an array loop, but by adding vars to the program as a character variable up front it resolved the issue. the working code now is -

%macro buildparameters(location=);
proc import datafile="&location." out=import dbms=xlsx; run;
data parameters; 
	set import (Where=(Run = "Y")); 
	check=tranwrd(check,".","");
	check=compress(trim("%"||check));
	format vars $500.;
	format call $200.;

	%do i=1 %to 10;
		%if var&i ne "" %then %do; 
			vars=compress(vars||var&i||",");
		%end;
	%end;
	vars="("||vars||")";
	vars=compress(tranwrd(vars,",,",""));
	if vars="(.)" then vars="";
	call=compress(check||vars||";");

run;
%mend buildparameters;

Thank you for all the help and responses. 

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Data step tries to convert character data to numeric and then fails.

I suspect this code

 

%do i=1 %to 10;
    %if var&i ne "" %then %do;
       vars=compress(vars||var&i||",");
    %end;
%end;

 

may be mixing macro code with datastep. The MACRO if (%IF) cannont evaluate the value of a datastep varialbe. SO the TEXT "var1" is Always ne ""  

It would help to show the entire log with the line numbers as the message will likely make much more sense.

You VARS variable is being treated as numeric. Does it exist in the import data set?

New Contributor
Posts: 4

Re: Data step tries to convert character data to numeric and then fails.

The full log - 

35   %macro buildparameters(location=);
36   proc import datafile="&location." out=import dbms=xlsx; run;
37   data parameters;
38       set import (Where=(Run = "Y"));
39       check=tranwrd(check,".","");
40       check=compress(trim("%"||check));
41       format call $200.;
42
43
44
45       %do i=1 %to 10;
46           %if var&i ne "" %then %do;
47               vars=compress(vars||var&i||",");
48           %end;
49       %end;
50
51       vars=compress(tranwrd(vars,",,",""));
52       call=compress(check||"("||vars||");");
53
54   run;
55   %mend buildparameters;
56   %buildparameters(location=C:\Users\tkukulka\Desktop\Code Test\validationtest.xlsx);

NOTE: The import data set has 67 observations and 12 variables.
NOTE: WORK.IMPORT data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds



NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      3:28    5:28    7:28    9:28    11:28   13:28   15:28   17:28   19:28   21:28   23:29
      23:75
NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      3:19    5:19    7:19    9:19    11:19   13:19   15:19   17:19   19:19   21:19   23:12
NOTE: Invalid numeric data, '.,' , at line 3 column 19.
NOTE: Invalid numeric data, '.,' , at line 5 column 19.
NOTE: Invalid numeric data, '.,' , at line 7 column 19.
NOTE: Invalid numeric data, '.,' , at line 9 column 19.
NOTE: Invalid numeric data, '.,' , at line 11 column 19.
NOTE: Invalid numeric data, '.,' , at line 13 column 19.
NOTE: Invalid numeric data, '.,' , at line 15 column 19.
NOTE: Invalid numeric data, '.,' , at line 17 column 19.
NOTE: Invalid numeric data, '.,' , at line 19 column 19.
NOTE: Invalid numeric data, '.,' , at line 21 column 19.
Run=Y Check=%DM10 Var1=  Var2=  Var3=  Var4=  Var5=  Var6=  Var7=  Var8=  var9=  var10=
call=%DM10(.); vars=. _ERROR_=1 _N_=1
NOTE: Invalid numeric data, '.dmdata=A,' , at line 3 column 19.
NOTE: Invalid numeric data, '.dmsubj=B,' , at line 5 column 19.
NOTE: Invalid numeric data, '.dmusubj=C,' , at line 7 column 19.
NOTE: Invalid numeric data, '.dmsite=D,' , at line 9 column 19.
NOTE: Invalid numeric data, '.dmbirth=E,' , at line 11 column 19.
NOTE: Invalid numeric data, '.dmsex=F,' , at line 13 column 19.
NOTE: Invalid numeric data, '.vsdata=G,' , at line 15 column 19.
NOTE: Invalid numeric data, '.Test=H,' , at line 17 column 19.
NOTE: Invalid numeric data, '.vsheight=I,' , at line 19 column 19.
NOTE: Invalid numeric data, '.vsweight=J,' , at line 21 column 19.
Run=Y Check=%DM21 Var1=dmdata=A Var2=dmsubj=B Var3=dmusubj=C Var4=dmsite=D Var5=dmbirth=E
Var6=dmsex=F Var7=vsdata=G Var8=Test=H var9=vsheight=I var10=vsweight=J call=%DM21(.); vars=.
_ERROR_=1 _N_=2
NOTE: Invalid numeric data, '.,' , at line 3 column 19.
NOTE: Invalid numeric data, '.,' , at line 5 column 19.
NOTE: Invalid numeric data, '.,' , at line 7 column 19.
NOTE: Invalid numeric data, '.,' , at line 9 column 19.
NOTE: Invalid numeric data, '.,' , at line 11 column 19.
NOTE: Invalid numeric data, '.,' , at line 13 column 19.
NOTE: Invalid numeric data, '.,' , at line 15 column 19.
NOTE: Invalid numeric data, '.,' , at line 17 column 19.
NOTE: Invalid numeric data, '.,' , at line 19 column 19.
NOTE: Invalid numeric data, '.,' , at line 21 column 19.
Run=Y Check=%MH10 Var1=  Var2=  Var3=  Var4=  Var5=  Var6=  Var7=  Var8=  var9=  var10=
call=%MH10(.); vars=. _ERROR_=1 _N_=3
NOTE: Invalid numeric data, '.,' , at line 3 column 19.
NOTE: Invalid numeric data, '.,' , at line 5 column 19.
NOTE: Invalid numeric data, '.,' , at line 7 column 19.
NOTE: Invalid numeric data, '.,' , at line 9 column 19.
NOTE: Invalid numeric data, '.,' , at line 11 column 19.
NOTE: Invalid numeric data, '.,' , at line 13 column 19.
NOTE: Invalid numeric data, '.,' , at line 15 column 19.
NOTE: Invalid numeric data, '.,' , at line 17 column 19.
NOTE: Invalid numeric data, '.,' , at line 19 column 19.
NOTE: Invalid numeric data, '.,' , at line 21 column 19.
Run=Y Check=%VS20 Var1=  Var2=  Var3=  Var4=  Var5=  Var6=  Var7=  Var8=  var9=  var10=
call=%VS20(.); vars=. _ERROR_=1 _N_=4
NOTE: Invalid numeric data, '.,' , at line 3 column 19.
NOTE: Invalid numeric data, '.,' , at line 5 column 19.
NOTE: Invalid numeric data, '.,' , at line 7 column 19.
NOTE: Invalid numeric data, '.,' , at line 9 column 19.
NOTE: Invalid numeric data, '.,' , at line 11 column 19.
NOTE: Invalid numeric data, '.,' , at line 13 column 19.
NOTE: Invalid numeric data, '.,' , at line 15 column 19.
NOTE: Invalid numeric data, '.,' , at line 17 column 19.
NOTE: Invalid numeric data, '.,' , at line 19 column 19.
NOTE: Invalid numeric data, '.,' , at line 21 column 19.
Run=Y Check=%CM20 Var1=  Var2=  Var3=  Var4=  Var5=  Var6=  Var7=  Var8=  var9=  var10=
call=%CM20(.); vars=. _ERROR_=1 _N_=5
NOTE: There were 5 observations read from the data set WORK.IMPORT.
      WHERE Run='Y';
NOTE: The data set WORK.PARAMETERS has 5 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.04 seconds


In my test data there's only 5 checks selected to run, only one of the checks has variables that need to be provided in the macro call. The Import data step before processing looks like this -

Data Snip 1.JPG

 

After processing and the errors shown in the log above it looks like this -

Data Snip 2.JPG

 

In the import dataset all of the Var1-10 variables are correctly identified as Character, the conversion to numeric is being forced somewhere inside of the Parameters data step. 

Super User
Super User
Posts: 7,039

Re: Data step tries to convert character data to numeric and then fails.

This block of code doesn't make any sense.

 

%if var&i ne "" %then %do;
        vars=compress(vars||var&i||",");
%end;

The string 'VAR1' or 'VAR2' is never going to equal the string '""'.

New Contributor
Posts: 4

Re: Data step tries to convert character data to numeric and then fails.

The spreadsheet allows each macro to have up to 10 allowable variables. Not every macro has the same number of variables, most actually have no variables at all.

 

That block is checking that variable VAR&i is not equal to an empty string, if it is then it doesn't do anything for the current iteration of VAR&i, if it isn't then it appends VAR&i to another variable called VARS and appends a comma to the end.   For example if I had a check on there called err1.0 with four variable that are required. The spreadsheet would have the following entries for that line -

Check=err1.0

VAR1 = a=(something)

VAR2 = b=(Something)

VAR3 = c=(Something)

VAR4 = c=(Something)

VAR5 = (empty)

VAR6 = (empty)

VAR7 = (empty)

VAR8 = (empty)

VAR9 = (empty)

VAR10 = (empty)

 

The program should then look at that line and do the following steps -

  1. Remove the decimal from the check value making Check=err10
  2. Append a % to the front of check making Check=%err10
  3. Check that VAR1 is not equal to an empty string
    1. Since this is true then Vars=a=(something)
  4. Repeat step 3 through VAR10, Vars at the end would be string readin - a=(something),b=(something),c=(something),d=(something)
  5. The program then wraps Vars in parenthesis so Vars=(a=(something),b=(something),c=(something),d=(something))
  6. The program then checks vars to make sure it didn't stick two empty values together which would result in double commas. If it did it removes the double comma and replaces it with an empty space, then compresses out the empty spaces in the string.
  7. The program then appends Check,Vars, and a semicolon together and compresses out an extra spaces so now Call=%err10(a=(something),b=(something),c=(something),d=(something));

Call at the end of this program should be a list of the actual macro calls with all of the variables defined. A second program then reads this dataset, takes the value of call and puts it into a blank .SAS file along with some mapping, and include statements, saves it to the project directory, and then the top level program includes the newly saved file which automatically runs all of the macros.  The second program works. If I just put the call column into the excel spreadsheet that drives all of this, import it and then feed it into the second program that's fine. The problem is coming from trying to dynamically build the macro calls based on the other variables. 

Super User
Posts: 11,343

Re: Data step tries to convert character data to numeric and then fails.

Because you wrap the first possible appearance of the variable VARS in that never true macro code the first time the datastep sees the variable Vars is in the line

vars="("||vars||")";

By default undefined variables default to numeric. Since VARS is therefore numeric for all steps any thing that attempts to use VARS as a result will attempt to be turned into numeric values and fail miserably with your data.

See this short example:

data example;
   vars="("||vars||")";
run;

As several of use have mentioned your use of a macro %IF is extremely suspect. It probably should be a datastep loop over an array referencing the Var1-var10.

 

Perhaps you wanted:

array v var1-var10;
do i=1 to dim(v);
   if v[i] ne "" then vars= compress(vars||v[i]||",");
end;
Solution
‎08-22-2016 03:06 PM
New Contributor
Posts: 4

Re: Data step tries to convert character data to numeric and then fails.


ballardw wrote:

Because you wrap the first possible appearance of the variable VARS in that never true macro code the first time the datastep sees the variable Vars is in the line

vars="("||vars||")";

By default undefined variables default to numeric. Since VARS is therefore numeric for all steps any thing that attempts to use VARS as a result will attempt to be turned into numeric values and fail miserably with your data.

See this short example:

data example;
   vars="("||vars||")";
run;

As several of use have mentioned your use of a macro %IF is extremely suspect. It probably should be a datastep loop over an array referencing the Var1-var10.

 

Perhaps you wanted:

array v var1-var10;
do i=1 to dim(v);
   if v[i] ne "" then vars= compress(vars||v[i]||",");
end;

 

 

This is actually answer sort of. I haven't tried it changed from the %do %if loop to an array loop, but by adding vars to the program as a character variable up front it resolved the issue. the working code now is -

%macro buildparameters(location=);
proc import datafile="&location." out=import dbms=xlsx; run;
data parameters; 
	set import (Where=(Run = "Y")); 
	check=tranwrd(check,".","");
	check=compress(trim("%"||check));
	format vars $500.;
	format call $200.;

	%do i=1 %to 10;
		%if var&i ne "" %then %do; 
			vars=compress(vars||var&i||",");
		%end;
	%end;
	vars="("||vars||")";
	vars=compress(tranwrd(vars,",,",""));
	if vars="(.)" then vars="";
	call=compress(check||vars||";");

run;
%mend buildparameters;

Thank you for all the help and responses. 

Super User
Super User
Posts: 7,039

Re: Data step tries to convert character data to numeric and then fails.

[ Edited ]

There are still a lot of strange code that I suspect means that you still do not understand what the statements are doing.

 

These two statements I think are trying to remove spaces and periods and prefix a percent sign.  Note that the TRIM() function does nothing in the second statement. It isn't doing any harm, but it istn't doing anything either.

check=tranwrd(check,".","");
check=compress(trim("%"||check));

Which is easier to understand if you just use compress to remove both the periods and the spaces.  

check='%' || compress(check,' .') ;

The next two statements I think you meant to use to define those variables. The FORMAT statement will only define the variables as a side effect ot attaching the format.  Note you normally do NOT want to attach $xx formats to character variables. It really adds nothing and could protentially cause truncation of values on printing if you combine with another dataset that has defined the variable with a different length. 

length vars $500 call $200 ;

The %IF statement is doing nothing since the condition is always true. So the %DO loop always generates 10 assignment statements.  But you probably just want to replace the whole thing with one statement that uses the CATX() function to generate a comma separated list.

vars = catx(',',of var1-var10);

The CATX() function will also take care of much of the code in the last few statements. It looks like you want to add parentheses and a trailing semi-colon.

call = cats(check,'(',vars,');');

 You could just to it all at once if you want, no need to modify CHECK or build the new variable VARS.

 

length call $500 ;
call=cats('%',compress(check,'. '),'(',catx(',',of var1-var10),');');
Super User
Posts: 19,770

Re: Data step tries to convert character data to numeric and then fails.

Do you need a macro %do loop there or,would an array with a do loop work just as well?

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 970 views
  • 0 likes
  • 4 in conversation