DATA Step, Macro, Functions and more

Character to numeric

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Character to numeric

Hi,

 

I have two charact variables that I want to change to numeric.

 

The first has continuous numbers in it, but also has 4 charact. inputs. I would like to keep the continous numbers and change the character to missing.

 

I tried creating new variable with format and the next code but did not work. Also compress method tells me : too many arguments.

 

FORMAT NEW_VAR1   3.;

IF VAR1= 'N/A' THEN NEW_VAR1 = . ;
IF VAR1= 'Unk' THEN NEW_VAR1 = . ;
IF VAR1= 'n/a' THEN NEW_VAR1 = . ;
IF VAR1= 'unk' THEN NEW_VAR1 = . ;
IF VAR1= '' THEN NEW_VAR1 = . ;
ELSE NEW_VAR1 = VAR1;

 

The second variable is smoking variable, it has 5 values as below :


FORMAT  SMOKING  3.;

 

IF CIGARETTE= 'NEVER' THEN SMOKING = 0 ;
IF CIGARETTE= 'BEFORE' THEN SMOKING = 1 ;
IF CIGARETTE= 'NOW' THEN SMOKING = 2 ;
IF CIGARETTE= 'EVERYDAY' THEN SMOKING = 2 ;
IF CIGARETTE= 'UNKNOWN' THEN SMOKING = . ;
IF CIGARETTE= '' THEN SMOKING = . ;

 

The ERROR log :

 

NOTE: Character values have been converted to numeric values at the places given by:
(Line)Smiley SadColumn).
2404:5 2460:6 2461:16 2471:6 2472:19
NOTE: Invalid numeric data, ........

 

Is there something that I'm missing?

 

Thanks,

 

 

 


Accepted Solutions
Solution
‎04-07-2017 11:54 AM
PROC Star
Posts: 7,363

Re: Character to numeric

Actually, it did work, but the log showed all of the records where alpha characters were shown. You can do it suppressing those errors with the following:

 

libname thedata '/folders/myfolders';

DATA EXMED_NOTMULTI_3;
  set thedata.exmed_test1 (rename=(distancetestmarche6minfinprogram=_d));
  distancetestmarche6minfinprogram=input(compress(_d,'kd'),?? 12.);
run;

proc freq data=EXMED_NOTMULTI_3 ;
  tables distancetestmarche6minfinprogram;
  where not missing(distancetestmarche6minfinprogram);
run;

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: Character to numeric

Would help if you provide some example data and what you want as a result (based on that example data).

 

You will likely need to use the input function rather than just have the new numeric variable equal the original character variable, but if there are any instances you haven't accounted for with your if statements, you'll also have to use compress or a similar function to eliminate the characters in the variable.

 

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 40

Re: Character to numeric

Hi,

 

Hi,

 

Thanks for the reply, 

I tried compress before, it worked for another variables but not the ones with 5 character values, it said (error too many arguments)

 

I'm uploading a tata file with the two variables that need to be reformatted.

 

Thanks,

 

 

Contributor
Posts: 40
Contributor
Posts: 40

Re: Character to numeric

The attachement is not working, I uploaded a onedrive link. It's from my university account (secured)
Super User
Posts: 17,840

Re: Character to numeric

Which lines is the log referring to?

 

You can simplify your code by using IN

 

if var1 in ('N/A', 'Unk', ....etc) then new_Var1=.;

else ...

 

 

Contributor
Posts: 40

Re: Character to numeric

Hi,

 

Thanks for the reply, 

I tried it but it did not work. 

 

I'll upload a data file wit the two variables

Contributor
Posts: 40

Re: Character to numeric

Hi,

Thanks for the reply,
I tried it but it did not work.

I'll upload a data file wit the two variables
Super User
Posts: 5,084

Re: Character to numeric

Most likely, your data contains additional values for your character variables that your programming statements must take into account.  Try running this program to see a list of all of those:

 

proc freq data=have;

tables var1;

where var1 > ' ' and input(var1, ??8.) = .;

run;

 

Unfortunately, you need to run this type of program for each variable separately.  But it will point out what you need to work on.

 

Alternatively, you could just perform the conversion without worrying about which values can't be converted:

 

new_var1 = input(var1, ??8.);

 

The ?? suppresses messages about invalid data when trying to convert from character to numeric.

Contributor
Posts: 40

Re: Character to numeric

Hi,

 

Thanks for the reply, 

I tried it but it did not work. 

 


386 proc freq data=EXMED_NOTMULTI_3;
387 tables TAS3moisvisite1;
388 where TAS3moisvisite1 > ' ' and input(TADdebutvisite1, ??8.) = . ;
-
22
200
ERROR: Erreur de syntaxe détectée lors de l'analyse de la clause WHERE.
ERROR 22-322: Expecting un nom de format.
ERROR 200-322: The symbol is not recognized and will be ignored.
389 run;

 

 

sorry for the french part,

 

 

I'll upload a data file wit the two variables

Super User
Posts: 5,084

Re: Character to numeric

Looks like you changed the variable name.  Use the same variable name throughout the WHERE statement.

Contributor
Posts: 40

Re: Character to numeric

Sorry for that,

I was trying several variables. It gives me the same error message

391 proc freq data=EXMED_NOTMULTI_3;
392 tables TADdebutvisite1;
393 where TADdebutvisite1 > ' ' and input(TADdebutvisite1, ??8.) = . ;
-
22
200
ERROR: Erreur de syntaxe détectée lors de l'analyse de la clause WHERE.
ERROR 22-322: Expecting un nom de format.
ERROR 200-322: The symbol is not recognized and will be ignored.
394 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


Contributor
Posts: 40

Re: Character to numeric

The attachement is not working, I uploaded a onedrive link. It's from my university account (secured)

Contributor
Posts: 40
Super User
Posts: 5,084

Re: Character to numeric

Guessing at the meaning of the notes in the log ...

 

I would guess that you are using the name a numeric variable in the TABLE and WHERE statements.  You need to be using the character variable that you are trying to turn into a set of numeric values.

☑ This topic is SOLVED.

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

Discussion stats
  • 29 replies
  • 215 views
  • 4 likes
  • 7 in conversation