topic Re: coverting a character variable with number and missing values to numeric variables in New SAS User

coverting a character variable with number and missing values to numeric variables

Gilles-Protais — Thu, 18 May 2023 08:33:10 GMT

Greetings everyone

I have a dataset with a variable with missing values called NA. Because of this, it makes that variable a character variable, so I don't know how to make this numerical so that I can use multiple imputations to impute the missing data.

But I removed the Na and replaced it with a space; it still shows that the variable is a character variable, same with the replacing with the dot,

Please, what can I do to handle this? Thanks.

making a character variable with numbers and missing values(NA) to be numeric.

Gilles-Protais — Thu, 18 May 2023 08:30:21 GMT

Greetings everyone

But I removed the Na and replaced it with a space; it still shows that the variable is a character variable, same with the replacing with the dot,

Please, what can I do to handle this? Thanks.

Re: coverting a character variable with number and missing values to numeric variables

Kurt_Bremser — Thu, 18 May 2023 09:11:41 GMT

From which source do you import the data into SAS?

Please do not post the same question multiple times. It only makes following the flow of the discussion harder, so I merged your posts.

Re: coverting a character variable with number and missing values to numeric variables

Gilles-Protais — Thu, 18 May 2023 10:45:38 GMT

Yes, I want to import from SAS

Re: coverting a character variable with number and missing values to numeric variables

Kurt_Bremser — Thu, 18 May 2023 10:59:10 GMT

You can not import from SAS. You either import into SAS, or export from SAS.

If you have an external data source containing "NA" for missing values, you use an informat during the import process which reads "NA" as missing, and everything else as numbers.

Re: coverting a character variable with number and missing values to numeric variables

Gilles-Protais — Thu, 18 May 2023 12:47:39 GMT

Yes it is an external data source. Precisely csv

Re: coverting a character variable with number and missing values to numeric variables

Gilles-Protais — Thu, 18 May 2023 12:49:22 GMT

Thanks very much, but can i have the code format, to import the csv file as informal?,

Re: coverting a character variable with number and missing values to numeric variables

Kurt_Bremser — Thu, 18 May 2023 13:13:15 GMT

First, create an informat:

proc format;
invalue mynum
  'NA' = .
  other = [best20.]
;
run;

Then, use it while you read the file:

data want;
infile "path_to_your_file.csv" dlm="," dsd truncover firstobs=2;
input
  ...
  numvar :mynum.
  ...
;
run;

Re: coverting a character variable with number and missing values to numeric variables

Tom — Thu, 18 May 2023 13:38:57 GMT

This has been asked before. You could try search for the previous questions and answers.

In general there are two methods.

1) Make version of the text file without the NA strings. Then use what every method you want to read the converted file.

Example:

filename csv 'my original file.csv';
filename fixed 'my original file fixed.csv';
data _null_;
  infile csv ;
  file fixed;
  input ;
  _infile_=tranwrd(cats(',',_infile_,','),',NA,',',,');
  _infile_=substrn(_infile_,2,length(_infile_)-2);
  put _infile_;
run;

2) Write your own data step to read the data instead of forcing SAS to GUESS how to read the file.

If you write your own data step you get to decide which variables are numeric and which are character. If you don't do anything else then SAS will write a note when it sees one of those NA strings in a field being read as a numeric value, but the result will be a missing value, which is what you want.

Reading a CSV file is TRIVIAL with a data step. So there is no need to resort to forcing SAS to GUESS how to read it. Just define the variables. Attach any NEEDED formats or informats. Then read the variables.

Example:

data want;
  infile csv dsd truncover firstobs=2;
  length id $10 age 8 start_date 8 ;
  informat start_date yymmdd. ;
  format start_date yymmdd10.;
  input id -- start_date ;
run;

To suppress the notes about invalid values you could use the ?? modifier on the INPUT statement. But note that will suppress ALL of the warnings about invalid values. So values like FRED will be silently set to missing just like values like NA.

Of you could define a special INFORMAT that will convert the NA strings to missing and then use normal informat for the other values.

proc format ;
invalue na     'NA','na','Na' = . other = [32.] ;
invalue na_ymd 'NA','na','Na' = . other = [yymmdd10.] ;
run;

Then use the informat in the data step.

data want;
  infile csv dsd truncover firstobs=2;
  length id $10 age 8 start_date 8 ;
  informat start_date na_ymd.  age na.;
  format start_date yymmdd10.;
  input id -- start_date ;
run;

Re: coverting a character variable with number and missing values to numeric variables

Gilles-Protais — Thu, 18 May 2023 13:44:19 GMT

Thank you for the answer,

but can easily decode what you have written, because still new to was coding.

For instance if the columns of my csv file where a,b,c and c is the numeric with missing values how could I fit them into the code you given me. so sorry if am too much asking questions.

Re: coverting a character variable with number and missing values to numeric variables

Tom — Thu, 18 May 2023 14:01:33 GMT

To read a CSV file with 4 columns. Three character strings with max lengths of 10,20 and 300 followed by one numeric variable that has NA instead of null strings to represent missing you could do:

proc format;
invalue na 'NA'=. other=[32.];
run;
data want;
  infile 'myfile.csv' dsd truncover firstobs=2;
  length a $10 b $20 c $300 D 8;
  input a b c d :na. ;
run;

Re: coverting a character variable with number and missing values to numeric variables

Kurt_Bremser — Thu, 18 May 2023 14:00:12 GMT

The columns of the csv file must appear in the correct order (as stated in the documentation/description of the file) in the INPUT statement, with informats if needed. See the documentation of List INPUT

Re: coverting a character variable with number and missing values to numeric variables

Kurt_Bremser — Thu, 18 May 2023 14:02:10 GMT

Open your csv file with a text editor (not with Excel!), and copy/paste the first few lines into a window opened with this button:

Re: coverting a character variable with number and missing values to numeric variables

Gilles-Protais — Thu, 18 May 2023 14:55:44 GMT

"subplotID","flowerID","garden","species","rater","compound","tot.vase.days"
1,"11812",1,1,8,1,25
2,"11713",1,1,7,1,10
3,"11611",1,1,6,1,25
4,"11411",1,1,4,1,23
5,"11112",1,1,1,1,NA
6,"11113",1,1,1,1,NA

these are the few lines of my csv file

Re: coverting a character variable with number and missing values to numeric variables

Kurt_Bremser — Thu, 18 May 2023 15:02:43 GMT

Looks like everything except the last column is for identification purposes and should therefore be stored as character.

data want;
infile "......" dlm="," dsdtruncover firstobs=2;
input
  subplotID $
  flowerID $
  garden $
  species $
  rater $
  compound $
  tot_vase_days :mynum.
;
run;

Re: coverting a character variable with number and missing values to numeric variables

Tom — Thu, 18 May 2023 15:06:25 GMT

For a simple file like that I wouldn't bother with doing anything fancy about the NA values.

In general the process I follow it to take first line from the file and paste it into the program editor. Remove the quotes and commas (and fix any column headers that are not valid variable names). Then add LENGTH at the front and insert length specifications for each variable so I can convert it into a LENGTH statement to DEFINE the variables.

But for something this simple I might just use in-line informat specifications in the INPUT statement and let SAS impute the variable type and length from the type and width of the informat used to read the variable. That is because the first 6 variables are all identifier variables and so should be defined as character since you will never be doing arithmetic with the values. To suppress the error messages from the NA value in the one numeric variable I would just add the ?? modifier to the INPUT statement.

data want;
  infile 'myfile.csv' dsd truncover firstobs=2;
  input (subplotID flowerID garden species rater compound) (:$8.) tot_vase_days ??;
run;

Re: coverting a character variable with number and missing values to numeric variables

Gilles-Protais — Thu, 18 May 2023 15:26:23 GMT

Thank you very much.

It Worked for me now.