Convert column type from character to numeric in SAS enterprise miner

[Jack_FG]

Hi All,

I am currently importing an xlsx file into SAS EM using the file import node.

When I check my variables after the import has run, a number of continuous variables have been labelled as nominal and character type.

I have a number of other variables that are exactly the same (i.e. numeric) that have been successfully imported.

I am unable to work out how to change the type from character to numeric in SAS EM, any thoughts?

[Astounding · Accepted solution]

If the importing process "decided" to make these character variables, it probably has a reason.  It would pay to look at the cases where a numeric is not possible, and possibly fix the data on the Excel side before importing.  Here's an easy way to find the trouble makers:

proc freq data=already_imported;

   tables troublesome_charvar;

   where troublesome_charvar > ' ' and input(troublesome_charvar, ??32.) = .;

run;

[Jack_FG]

I had a cheeky NA in one of my numeric columns - hence the problem. Thankyou.

[tnaveen80150]

Is there an error in this code? I tried implementing it for a similar problem that I am facing and received the following error/s.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 proc freq data=Quartda.quart_data_35yrs_pruned;

72

73 tables invrmq;

74

75 where invrmq > ' ' and input(invrmq, ??32) = . ;

_

22

76

ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Expecting a format name.

ERROR 76-322: Syntax error, statement will be ignored.

76

77 run;

[paulkaefer]

@tnaveen80150, the format needs a period (.) at the end, so:

??32.

[tnaveen80150]

I get the same error with the period! A SAS newbie here, so struggling here. Thanks for your patience.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 proc freq data=Quartda.quart_data_35yrs_pruned;

72

73 tables invrmq;

74

75 where invrmq > ' ' and input(invrmq, ??32.) = . ;

[paulkaefer]

I haven't used the ?? modifiers in the input statement. Here they show examples with space after the ??.

You might also just try replacing ??32. with something like best8., just to try it.

[tnaveen80150]

@paulkaefer

Tried it with the space too; did not work still. Neither did 'best8.' 

I am not sure what is wrong and am still confused. Thanks a lot for your help though.

[paulkaefer]

Hmm, this is a tricky one! Maybe @Astounding, who posted the original code snippet, can weigh in?

[Astounding]

Hmmm ... it looks like that suggestion works in an IF statement, but not in a WHERE statement.  To avoid creating an entirely new data set, feed a view in to PROC FREQ:

data test / view=test;

set already_imported (keep=troublesome_charvar);

if troublesome_charvar > ' ' and input(troublesome_charvar, ??32.) = .;

run;

proc freq data=test;

   tables troublesome_charvar;

run;

[tnaveen80150]

@Astounding @paulkaefer

That worked !!! Indeed, it seems to work with IF and not WHERE.