Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Convert column type from character to numeric in SAS enterprise miner

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Convert column type from character to numeric in SAS enterprise miner

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?


Accepted Solutions
Solution
‎12-11-2017 04:06 AM
Super User
Posts: 6,778

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

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;

View solution in original post


All Replies
Solution
‎12-11-2017 04:06 AM
Super User
Posts: 6,778

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

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;

New Contributor
Posts: 2

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

Posted in reply to Astounding

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

Occasional Contributor
Posts: 11

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

Posted in reply to Astounding

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;
Regular Contributor
Posts: 215

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

Posted in reply to tnaveen80150

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

??32.
Occasional Contributor
Posts: 11

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

Posted in reply to paulkaefer
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.) = . ;
Regular Contributor
Posts: 215

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

Posted in reply to tnaveen80150

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.

Occasional Contributor
Posts: 11

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

Posted in reply to paulkaefer

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

Regular Contributor
Posts: 215

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

Posted in reply to tnaveen80150

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

Super User
Posts: 6,778

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

Posted in reply to paulkaefer

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;

Highlighted
Occasional Contributor
Posts: 11

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

Posted in reply to Astounding

@Astounding @paulkaefer

 

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

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 997 views
  • 3 likes
  • 4 in conversation