topic Changing values of SAS column in SAS Programming

Changing values of SAS column

aalluru — Thu, 17 Dec 2020 15:14:04 GMT

I have a dataset with a column called "Type" which mainly consists of 2 values - "Ind" and "Fran". I would like to make that column numeric such that the value "Ind" is replaced with 1 and the value "Fran" is replaced with 0.

Would it be more feasible to use a proc format or to use if-then statements in a data step? Could you give me an example of what the data step would look like considering that the datatype of the column will also have to be changed?

Re: Changing values of SAS column

PaigeMiller — Thu, 17 Dec 2020 15:18:25 GMT

You can use either PROC FORMAT or a data step.

PROC FORMAT does not make the variable numeric, the variable remains character, although it appears to us humans as 1 or 0. A data step would require you to create a new numeric variable that contains the numeric 0 and 1 values.

data want;
    set have;
    if type='Ind' then type1=1;
    else if type='Fran' then type1=0;
run;

Re: Changing values of SAS column

aalluru — Thu, 17 Dec 2020 15:21:53 GMT

proc format;
invalue Ind_Fran
'Ind' = 1
'Fran' = 0
other = .;
Run;

So you're saying that this code would not change the datatype?

Re: Changing values of SAS column

PaigeMiller — Thu, 17 Dec 2020 15:29:33 GMT

Actually, I was thinking of using VALUE and not INVALUE. VALUE does not change the variable type. I haven't tried it with INVALUE, but I think it leaves the variable type unchanged. Formats and informats just change the appearance of the variable.

So you're saying that this code would not change the datatype?

The quickest answer is to try it yourself.

Re: Changing values of SAS column

aalluru — Thu, 17 Dec 2020 15:29:51 GMT

Value is giving me an error

Re: Changing values of SAS column

PaigeMiller — Thu, 17 Dec 2020 15:32:47 GMT

@aalluru wrote:

Value is giving me an error

For your future benefit, please understand that saying something gives an error, and not providing details, never helps. This provides no useful information to diagnose the error.

We need to see the LOG for these steps — the entire log for these steps, with nothing chopped out. We need to see the log in a format that preserves the formatting and readability of the log. Copy the log as text, and paste it into the window that appears when you click on the </> icon. I have stopped trying to read logs where these instructions are not followed. DO NOT SKIP THIS STEP.

Re: Changing values of SAS column

aalluru — Thu, 17 Dec 2020 15:32:59 GMT

Would it be possible then for me to drop type and rename type1 to type?

Re: Changing values of SAS column

aalluru — Thu, 17 Dec 2020 15:36:06 GMT

oh alright. I am not planning on using format for what I need right now but I will keep that in mind in the future.

Re: Changing values of SAS column

Reeza — Thu, 17 Dec 2020 16:15:06 GMT

No, this creates an informat that can then be applied to a data set or column but changes nothing by itself.

@aalluru wrote:
proc format;
invalue Ind_Fran
'Ind' = 1
'Fran' = 0
other = .;
Run;
So you're saying that this code would not change the datatype?

You would need to add an INPUT() statement to get the conversion.

newVar = input(oldVar, ind_fran.);

Re: Changing values of SAS column

ballardw — Thu, 17 Dec 2020 15:57:18 GMT

@aalluru wrote:

Value is giving me an error

Once a SAS variable type is set you cannot change it.

The informat would have to be used when either 1) initially reading the data into SAS or 2) create a NEW variable.

Re: Changing values of SAS column

Reeza — Thu, 17 Dec 2020 15:59:12 GMT

@aalluru wrote:

oh alright. I am not planning on using format for what I need right now but I will keep that in mind in the future.

This isn't because someone doesn't want to help you, it's because it could be as simple as a missing semicolon or comma which is super easy to tell when you have the log but almost impossible without it. It helps you get answers faster.

Re: Changing values of SAS column

ballardw — Thu, 17 Dec 2020 16:40:38 GMT

@aalluru wrote:

Would it be possible then for me to drop type and rename type1 to type?

Yes.

data want;
   set have (rename=(var = oldvar));
   var = input(oldvar, informat.);
   drop oldvar;
run;