topic Re: Convert column type from character to numeric in SAS enterprise miner in SAS Data Science

Convert column type from character to numeric in SAS enterprise miner

Jack_FG — Wed, 06 Dec 2017 02:26:06 GMT

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?

Re: Convert column type from character to numeric in SAS enterprise miner

Astounding — Wed, 06 Dec 2017 02:45:01 GMT

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;

Re: Convert column type from character to numeric in SAS enterprise miner

Jack_FG — Mon, 11 Dec 2017 09:07:19 GMT

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

Re: Convert column type from character to numeric in SAS enterprise miner

tnaveen80150 — Mon, 05 Feb 2018 20:33:09 GMT

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;

71 proc freq data=Quartda.quart_data_35yrs_pruned;

73 tables invrmq;

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

ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Expecting a format name.

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

77 run;

Re: Convert column type from character to numeric in SAS enterprise miner

paulkaefer — Mon, 05 Feb 2018 20:35:44 GMT

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

??32.

Re: Convert column type from character to numeric in SAS enterprise miner

tnaveen80150 — Mon, 05 Feb 2018 21:12:01 GMT

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;

71 proc freq data=Quartda.quart_data_35yrs_pruned;

73 tables invrmq;

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

Re: Convert column type from character to numeric in SAS enterprise miner

paulkaefer — Mon, 05 Feb 2018 22:05:52 GMT

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.

Re: Convert column type from character to numeric in SAS enterprise miner

tnaveen80150 — Mon, 05 Feb 2018 22:30:46 GMT

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

Re: Convert column type from character to numeric in SAS enterprise miner

paulkaefer — Tue, 06 Feb 2018 14:53:26 GMT

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

Re: Convert column type from character to numeric in SAS enterprise miner

Astounding — Tue, 06 Feb 2018 19:00:31 GMT

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;

Re: Convert column type from character to numeric in SAS enterprise miner

tnaveen80150 — Tue, 06 Feb 2018 19:32:57 GMT

@Astounding @paulkaefer

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