topic Re: convert character to numeric in SAS Programming

convert character to numeric

CathyVI — Fri, 28 Jul 2023 17:18:29 GMT

Hello,

I would like to convert the following character values into numeric.

My values are in character;

Interst
Freeway
Pri Art
Min Art
Maj Col

Min Col
Local

First I want to convert to numeric then convert to 1,2,3 in the following order.

'Interst' = 1
'Freeway' = 2
'Pri Art' = 3
'Min Art' = 4
'Maj Col' = 5
'Min Col' = 6
'Local' = 7

I initially created a format and i ran an input statement in the data step but I didn't get the expected result. My results were blank for funtional_num. Please help!!

proc format;
  value $functional  'Interst' = 1
                     'Freeway' = 2
                     'Pri Art' = 3
                     'Min Art' = 4
                     'Maj Col' = 5
                     'Min Col' = 6
                     'Local'   = 7
                     ;
run;

data A;
length functional $10.;
input functional$ 1-10;
datalines;
Interst
Freeway
Pri Art
Min Art
Maj Col
Min Col
Local
; run;

data B;
set A;
functional_num = input(functional, 8.);
format functional_num functional.;
run;

Result obtained:

Obs functional functional_num
1 Interst .
2 Freeway .
3 Pri Art .
4 Min Art .
5 Maj Col .
6 Min Col .
7 Local .

Re: convert character to numeric

PaigeMiller — Fri, 28 Jul 2023 17:36:28 GMT

Its not clear what you are trying to do here. Normally formats are used to make the data "more readable", converting numbers (such as 1, 2, 3, ...) into something more meaningful to the audience or SAS programmer, such as "Freeway", "Pri Art", ...

You may have a valid reason why you would want to use the reverse of what I just described and disguise "Freeway" as the number 2, but I am skeptical this is a good thing to do. (There are cases where you are working with somewhat sensitive data, such as ethnicity, and you might want to disguise "Hispanic" as the number 2, but this doesn't seem to apply here).

Nevertheless, this works:

data B;
    set A;
    functional_num = functional;
    format functional_num $functional.;
run;

Note: Functional_num is still a character variable

If FUNCTIONAL_NUM really has to be numeric, then you need an INFORMAT to be used in the INPUT function, not a format.

proc format;
  invalue functional  'Interst' = 1
                     'Freeway' = 2
                     'Pri Art' = 3
                     'Min Art' = 4
                     'Maj Col' = 5
                     'Min Col' = 6
                     'Local'   = 7
                     ;
run;

data A;
length functional $10.;
input functional$ 1-10;
datalines;
Interst
Freeway
Pri Art
Min Art
Maj Col
Min Col
Local
;

data B;
    set A;
    functional_num = input(functional,functional.);
run;

Re: convert character to numeric

CathyVI — Sat, 29 Jul 2023 12:27:19 GMT

@PaigeMiller You are right about keeping the initial meaning names e.g local, minor collector etc. It is more meaningful. So I want to have the functional road class in this order:

Interstate

Freeway

Pri Art

Min Art

Maj Col

Min Col

Local.

So when I run a proc freq, I want it to be in this order. How do I do that ? Thanks

Re: convert character to numeric

PaigeMiller — Sat, 29 Jul 2023 13:22:43 GMT

PROC FREQ has an ORDER= option

If one of those doesn't work for you, then I would create the numeric value using the INFORMAT as I showed earlier, sort by this numeric value, and then run PROC FREQ on your character variable named FUNCTIONAL (not the numeric variable named FUNCTIONAL_NUM), using the ORDER=DATA option. The sorting causes Interstate to appear first, Freeway will appear second, and so on.

Side comment: ask the real question in your first post, in this case the real question is how to get PROC FREQ to put categories in a specific order; don't ask an indirect question about how to assign numbers to character string categories. Your original question is the XY Problem, and should be avoided.

Re: convert character to numeric

CathyVI — Sat, 29 Jul 2023 14:53:33 GMT

@PaigeMiller Thanks for the advice. Well my main goal hasn't been achieved yet. My goal is to change the order of functional in my dataset so that when I run a logistic model or an emperical logit plot I will get the result in the order I want. So I initially thought of changing the variable to numeric and then running the model on the numeric version. However, I wanted the variables in their original names just as you suggested.

The freq is just for me to check if the order changed. The method you proposed in your latest post did change the order in the numeric but it didn't change it in the logistic model. Also, I have other variables in my model so functional is not the only one. Looking forward to your response. Thank you

In summary this is what I have in my dataset, in this order but this is what I want in my dataset.

What I have in the dataset:

FUNCTIONAL:

Freeway

Interst

Local

Maj Col

Min Art

Min Col

Pri Art

What I want in my dataset

FUNCTIONAL

Interst

Freeway

Pri Art

Min Art

Maj Col

Min Col

Local

Re: convert character to numeric

Tom — Sat, 29 Jul 2023 15:42:29 GMT

So that is a different question.

How does the order the data sorts impact the result of the logistic regression? The only thing I can think of is that it could change which value it considers as the comparison group. Can't you just specify which value to use?

What procedure are you using to run logistic regression?

Re: convert character to numeric

PaigeMiller — Sat, 29 Jul 2023 16:58:43 GMT

When you run a logistic regression, you can specify which level of a variable is the control. In addition, PROC LOGISTIC has the same ORDER= option that PROC FREQ has.

Re: convert character to numeric

Schalk_Burger — Mon, 25 Mar 2024 06:57:30 GMT

Is there a way to achieve this by not using the input function?

Re: convert character to numeric

ballardw — Mon, 25 Mar 2024 07:27:40 GMT

@Schalk_Burger wrote:

Is there a way to achieve this by not using the input function?

You should start your own thread an mention this one as related.

Likely a number of choices available:

In a data step use a bunch of If/then/else statements to assign values conditionally. Or Select/when.

Or use a custom informat when reading the data in the first place.

Actual example of values you have and the desired numeric values, and perhaps a description of why you need the numeric values, may get a more concrete answer.

Re: convert character to numeric

Schalk_Burger — Mon, 25 Mar 2024 11:34:41 GMT

Thanks @ballardw. Will do. Not that important, but will start a new thread.