BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jack_FG
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

10 REPLIES 10
Astounding
PROC Star

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
Calcite | Level 5

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

tnaveen80150
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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

??32.
tnaveen80150
Obsidian | Level 7
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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

@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
Lapis Lazuli | Level 10

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

Astounding
PROC Star

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
Obsidian | Level 7

@Astounding @paulkaefer

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

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