topic Re: replacing char values with zero in SAS Programming

replacing char values with zero

Solly7 — Mon, 07 Jun 2021 15:10:46 GMT

Hi,

I need help in replacing the value 'I don't know how tall I am' from height column as well as 'I don't know how much I weigh' from the weight column with the 0 .

Data Have

policy_no height weight

1 I don't know how tall I am 60

2 1.5 I don't know how tall I am

Data Want

policy_no height Weight

1 0 60

2 1.5 0

Re: replacing char values with zero

PaigeMiller — Mon, 07 Jun 2021 15:26:13 GMT

In your data set, are the variables HEIGHT and WEIGHT character or numeric? Please run PROC CONTENTS on this data set and show us. Also, if either of these variables have had a format applied, please identify which formats are used.

Re: replacing char values with zero

ballardw — Mon, 07 Jun 2021 15:28:35 GMT

Are you intending to do any analysis or summaries with the "height" and "weight" as actual numeric values?

If so then you need create new variables

Perhaps something like:

data want;
   set have;
   numheight= input(height,?? 5.);
   if missing(numheight) then numheight=0;
   numweight= input(weight,?? 5.);
   if missing(numweight) then numweight=0;

run;

INPUT is the way to create numeric values from text. The ?? suppresses the invalid data messages you would normally see when attempting to read "I don't know my height" into a numeric value. The IF assumes the reason for missing is something like your text entries and sets 0.

Or change how you read the data into SAS to address such things earlier.

Re: replacing char values with zero

Solly7 — Mon, 07 Jun 2021 15:37:37 GMT

Hi, kindly see below proc contents below

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

1 ExternalReferenceId Char 255 $255. $255. ExternalReferenceId
2 Height Char 255 $255.
3 Weight Char 255 $255.

Re: replacing char values with zero

Solly7 — Mon, 07 Jun 2021 15:40:14 GMT

Hi, yes im intending to replace them with mean value for analysis

Re: replacing char values with zero

Tom — Mon, 07 Jun 2021 15:46:54 GMT

Then set them as MISSING, not a valid value like zero.

Make new numeric variables to store the actual numbers.

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

1 ExternalReferenceId Char 255 $255. $255. ExternalReferenceId
2 Height Char 255 $255.
3 Weight Char 255 $255.

Why are those fields length $255? The longest string that the normal SAS informat can read is 32 characters.

Here is a step that will create numeric variables HT and WT from your existing strings. Anything that is not a valid number is converted to missing.

data want;
  set have;
  ht = input(left(height),32.);
  wt = input(left(weight),32.);
run;

If you want to suppress the warnings about invalid data you can use the ?? prefix on the informat.

Re: replacing char values with zero

PaigeMiller — Mon, 07 Jun 2021 15:50:20 GMT

To answer your specific question, even though I think this is not a good thing to do:

data have2;
     set have;
      if height='I don''t know how tall I am' then height='0';
      if weight='I don''t know how tall I am' then weight='0';
run;

To do the calculation of means, you would have to convert these to numeric variables as stated by @ballardw , but it really doesn't make sense to set these to zero, when you should be setting these to missing.

Does it really say tall when weight is missing?

Re: replacing char values with zero

AMSAS — Mon, 07 Jun 2021 16:48:40 GMT

It's important to understand how SAS handles missing values, not understanding could lead to results you are not expecting:

See Understanding How SAS Handles Missing Values

Here's a simple example that helps explain it:


data _null_ ;
	/* Notice how the mean of 0,1,2,3 = 1.5 that's because SAS calculates the mean as (0+1+2+3)/4 = 6/4 = 1.5 */
	avg=mean(0,1,2,3) ;
	put "Mean of 0,1,2,3 = " avg ;
	/* Notice how the mean of .,1,2,3 = 2 that's because SAS calculates the mean as (1+2+3)/3 = 6/3 = 2 */
	/* Basically SAS doesn't include missing values when using functions */
	avg=mean(.,1,2,3) ;
	put "Mean of .,1,2,3 = " avg ;
run ;

Re: replacing char values with zero

Solly7 — Tue, 08 Jun 2021 13:45:08 GMT

thanks a lot

Re: replacing char values with zero

Brijesh4sas — Sun, 18 Jul 2021 18:12:54 GMT

data have;
input policy_no height $&100. weight $&100.;
datalines;
1 I don't know how tall I am 60
2 1.5 I don't know how tall I am
;
run;

data have2;
set have;
if anyalpha(height) then height='0';
if anyalpha(weight) then weight='0';
run;