BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
CathyVI
Pyrite | Level 9

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                            .

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
CathyVI
Pyrite | Level 9

@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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
CathyVI
Pyrite | Level 9

@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

Tom
Super User Tom
Super User

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?

 

 

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Schalk_Burger
Calcite | Level 5

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

ballardw
Super User

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

 

 

Schalk_Burger
Calcite | Level 5
Thanks @ballardw. Will do. Not that important, but will start a new thread.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1998 views
  • 0 likes
  • 5 in conversation