topic Re: My data has ######## as a missing character in SAS Programming

My data has ######## as a missing character

EricB40 — Sun, 17 Sep 2023 20:53:21 GMT

Hi everyone,

My data is from a big csv file and in that file, the authors used ####### as a missing value. How do I get sas to identify this as a missing value and not try to fill it in?

My code right now is:

proc import out=work.problems
datafile="/home/u63556826/sasuser.v94/problems.csv"
dbms=csv
replace;
getnames=YES;
run;

proc print data=work.problems (obs = 6); run;

Re: My data has ######## as a missing character

Quentin — Sun, 17 Sep 2023 21:02:56 GMT

While there are ways to do this when you read in the data, it will probably be easier to convert it after you have read the data into a SAS dataset.

So you could do:

data problems2;
  set problems ;
  array chars{*} _character_ ;
  do i=1 to dim(chars);
    if chars{i}='########' then call missing(chars{i});
  end;
run;

And if you want numeric variables, you could add code to create numeric variables from the character variables, using the INPUT function.

Re: My data has ######## as a missing character

EricB40 — Sun, 17 Sep 2023 21:30:20 GMT

Would that look something like this:

proc import out=work.problems
datafile="/home/u63556826/sasuser.v94/problems.csv"
dbms=csv
replace;
getnames=YES;
run;

data problems2;
set problems ;
array chars{*} _character_ ;
do i=1 to dim(chars);
if chars{i}='########' then call missing(chars{i});
end;
run;

proc print data=work.problems (obs = 6); run;

Re: My data has ######## as a missing character

Quentin — Sun, 17 Sep 2023 22:47:07 GMT

Yes, that was my thought. How does it work for your need?

Re: My data has ######## as a missing character

mkeintz — Mon, 18 Sep 2023 00:14:57 GMT

If the data are in a text or csv or other ascii file, you can use INFORMATs to tell SAS to convert '########' to missing values, both for numeric, and for character variables:

proc format ;
  invalue shnum '########'=.
           other=[best32.];
  invalue $shchr '########'=' '  min=8 max=20
           other=[$20.];
run;

data test;
  informat default=shnum. ;
  input id $5.  num1  num2  char1 :$shchr10. char2 :$shchr. ;
datalines;
AAAAA ######## 1002     ch11_6789A ch12_6789ABC
BBBBB 2001     2002     ########   ch22_6789ABC
CCCCC 3001     ######## ch31_6789A ########
DDDDD ######## ######## ########   ########
run;
proc print;
  var id num1 num2 char1 char2;
run;

Re: My data has ######## as a missing character

andreas_lds — Mon, 18 Sep 2023 05:54:01 GMT

Importing a csv file with non-standard missing values (standard = empty field) with proc import will lead to extra work and may lead to variables with a wrong guessed type. So better write a data step yourself to avoid all the things you have to do get the data step with proper typed variables (and lengths).

Re: My data has ######## as a missing character

ballardw — Mon, 18 Sep 2023 15:37:12 GMT

@andreas_lds wrote:

Importing a csv file with non-standard missing values (standard = empty field) with proc import will lead to extra work and may lead to variables with a wrong guessed type. So better write a data step yourself to avoid all the things you have to do get the data step with proper typed variables (and lengths).

Definitely agree. I have data sources where they can't even be consistent and one file will have: NA, not answered, UNK , unknown, refused to answer in ONE variable just for what should be missing values. Proc format can handle multiple strings like that to all set one standard "missing" value.