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,
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
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
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,
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 ...
Hi,
Thanks for the reply,
I tried it but it did not work.
I'll upload a data file wit the two variables
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.
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
Looks like you changed the variable name. Use the same variable name throughout the WHERE statement.
The attachement is not working, I uploaded a onedrive link. It's from my university account (secured)
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.