topic Re: Character to numeric in SAS Programming

Character to numeric

seltonsy — Thu, 06 Apr 2017 19:46:32 GMT

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):(Column).
2404:5 2460:6 2461:16 2471:6 2472:19
NOTE: Invalid numeric data, ........

Is there something that I'm missing?

Thanks,

Re: Character to numeric

art297 — Thu, 06 Apr 2017 19:55:06 GMT

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

Re: Character to numeric

Reeza — Thu, 06 Apr 2017 19:55:50 GMT

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

Re: Character to numeric

Astounding — Thu, 06 Apr 2017 20:07:11 GMT

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.

Re: Character to numeric

Yavuz — Thu, 06 Apr 2017 20:14:38 GMT

You can use compress with 'kd' argument:

Sample;
string='StudySAS Blog! 17752. ' ;
string11=compress(string,'','kd') ;

Result:
string11=17752

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 14:42:08 GMT

Hi,

Thanks for the reply,

I tried it but it did not work.

I'll upload a data file wit the two variables

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 14:43:21 GMT

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

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 14:44:11 GMT

Hi,

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

I'll upload a data file wit the two variables

Re: Character to numeric

Astounding — Fri, 07 Apr 2017 14:46:46 GMT

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

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 14:48:49 GMT

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,

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 14:51:04 GMT

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

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 14:55:23 GMT

https://liveumoncton-my.sharepoint.com/personal/pse2915_umoncton_ca/_layouts/15/guestaccess.aspx?docid=1d62a060b99a04863ab8db96a0038fe2e&authkey=AaTF79pYDzTtp1K5hvKjRWY

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 14:57:32 GMT

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

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 14:59:38 GMT

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

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 15:00:24 GMT

https://liveumoncton-my.sharepoint.com/personal/pse2915_umoncton_ca/_layouts/15/guestaccess.aspx?docid=1d62a060b99a04863ab8db96a0038fe2e&authkey=AaTF79pYDzTtp1K5hvKjRWY

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 15:07:10 GMT

I was wondering, does this blog allow sharing SAS data files or no? I was unable to do so, it said contents of file does not correspond with the name!?

Re: Character to numeric

art297 — Fri, 07 Apr 2017 15:11:31 GMT

The following worked for me:

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

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 15:41:24 GMT

Hello,

Thanks for the reply,

I tried it but it did not work (problem with INPUT) . I find it weird. here is the log.

407 DATA EXMED_test2;
408 set exmed_test1 (rename=(distancetestmarche6minfinprogram=_d));
409 distancetestmarche6minfinprogram=input(compress(_d,'kd'),12.);
410 run;

NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Dans passe ID=2 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=2
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=n/a Fumeacutellementadmission=Jamais ID=16 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=16
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Dans passe ID=85 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=85
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Dans passe ID=92 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=92
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Jamais ID=116 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=116
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Jamais ID=133 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=133
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Dans passe ID=141 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=141
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Jamais ID=155 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=155
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Jamais ID=159 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=159
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Presentemen ID=161 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=161
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Jamais ID=163 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=163
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Presentemen ID=165 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=165
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Unk Fumeacutellementadmission=Dans passe ID=172 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=172
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Dans passe ID=180 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=180
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Jamais ID=189 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=189
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Dans passe ID=190 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=190
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Dans passe ID=193 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=193
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=Inc Fumeacutellementadmission=Presentemen ID=194 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=194
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
_d=n/a Fumeacutellementadmission=Jamais ID=196 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=196
NOTE: Invalid argument to function INPUT at ligne 409 colonne 36.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
_d=Inc Fumeacutellementadmission=Jamais ID=205 distancetestmarche6minfinprogram=. _ERROR_=1
_N_=205
NOTE: Mathematical operations could not be performed at the following places. The results of
the operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
54 dans 409:36
NOTE: There were 410 observations read from the data set WORK.EXMED_TEST1.
NOTE: The data set WORK.EXMED_TEST2 has 410 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

Re: Character to numeric

seltonsy — Fri, 07 Apr 2017 15:46:12 GMT

Also , what about the other variable, this one has character values that I want to convert to categories, it should be straight forward:

Here is what I tried,

IF Fumeacutellementadmission = 'Jamais' THEN SMOKING = 0 ;
IF Fumeacutellementadmission = 'Dans passe' THEN SMOKING = 1 ;
IF Fumeacutellementadmission = 'Presentemen' THEN SMOKING = 2 ;
IF Fumeacutellementadmission = 'Chaque jour' THEN SMOKING = 2 ;
IF Fumeacutellementadmission = 'Inconnu' THEN SMOKING = . ;
IF Fumeacutellementadmission = . THEN SMOKING = . ;

Thanks

Re: Character to numeric

Astounding — Fri, 07 Apr 2017 15:49:00 GMT

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.