Import CSV file and keep the period, not treat period as missing value

enki37 — Wed, 21 Aug 2024 11:29:46 GMT

How to import the attached csv files and keep the "." , these is just the sample data and it delimited by "|" and contain a value "N|A"

"VAR1"|"VAR2"|"VAR3"
"(-)"|"NA"|"A"
"-"|"N|A"|"B"
"."|""|"C"
""|"na"|"D"

The below code will convert the "." as missing value, but I want the value VAR1 in row 3 is "."

options missing="*";
proc import datafile="test.csv"
    out=t1
    dbms=csv
    replace;
    delimiter='|';
    getnames=yes;
run;

data t2;
    infile "test.csv" delimiter='|' dsd flowover firstobs=2 missover /*truncover*/;
    input VAR1 :$20. VAR2 :$10.;
run;

Thank you very much.

Re: Import CSV file and keep the period, not treat period as missing value

Tom — Wed, 21 Aug 2024 12:07:18 GMT

There is no need to "import" a CSV file. Just write the data step needed to READ the file.

To avoid the normal conversion of a single period to a missing value you need to read the value using the $CHAR informat instead of the default $ informat.

data t2;
   infile "test.csv" dsd dlm='|' truncover firstobs=2 ;
   input VAR1 :$char20. VAR2 :$char10. var3 :$char1.;
run;

Result

Obs    VAR1    VAR2    var3

 1     (-)     NA       A
 2     -       N|A      B
 3     .                C
 4             na       D

PS You can only have one of FLOWOVER MISSOVER and TRUNCOVER. The last one listed will "win". The default is FLOWOVER. You almost never want the strange behavior of MISSOVER. So use TRUNCOVER when reading a delimited text file that might have an empty value for the last value on the line.

topic Import CSV file and keep the period, not treat period as missing value in SAS Programming

Import CSV file and keep the period, not treat period as missing value

Re: Import CSV file and keep the period, not treat period as missing value