SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
enki37
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

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.

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 735 views
  • 0 likes
  • 2 in conversation