Changing variable from char to numeric after deleting text entries?

[DamianSilas]

I have a variable, NSAT_M, that lists SAT scores. However, for those who are missing SAT scores, 'N/A' was entered into this variable column. Thus, when I imported the Excel into SAS, it identified NSAT_M as a character variable.

I was able to use the DELETE statement to remove any observations with the 'N/A' value in the NSAT_M field, but this does not solve my problem of NSAT_M still being considered a character variable.

Is there a way for me to change the variable to numeric, even though those 'N/A' entries still exist in the original file?

From what I've found online so far, this isn't possible. I realize that the easy way to do this would be to remove any 'N/A's from the original Excel file and then reimport it to SAS so that it's automatically characterized as a numeric variable, but (for some reason) my supervisor is telling me I need to figure out a way to do this directly in SAS without altering the Excel file.

Any help would be appreciated!

[novinosrin]

HI @DamianSilas  How about using a Custom informat and then do whatever once the new variable with the right values is set as numeric.

For example, _Same_ is read the value as is

proc format;
 invalue conv
 'N/A'=.
 other=_same_
 ;
run;
 data have;
  input var $;
 cards;
12
20
6
N/A
34
;

data want;
set have;
new_var=input(var,conv8.);
run;

[ed_sas_member]

Hi @DamianSilas 

Another approach could be:

data want (drop=var rename=(var_num = var));
	set have;
	if strip(var) = 'N/A' then var = '' ; /* var = character variable */
	var_num=input(var, best8.);
run;

[novinosrin]

@ed_sas_member  Good thinking. If you do want to try the traditional method, add the ?? modifier spice up your input function, so you don't have to conditionally execute 🙂 Have fun! cheers!

data have;
input var $;
cards;
12
20
6
N/A
34
;

data want (drop=var rename=(var_num = var));
	set have;
	*if strip(var) = 'N/A' then var = '' ; /* var = character variable */
	var_num=input(var,?? best8.);
run;

[ed_sas_member]

Thanks for the tip @novinosrin ! Always on top 👍