topic Re: Importing a .csv file with a blank column in SAS Programming

Importing a .csv file with a blank column

tomcmacdonald — Fri, 20 Apr 2018 14:22:24 GMT

Here's what I'm trying to do:

DATA FOO;
  INFILE '/path/to/csv/file' DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2;
  INFORMAT ID 10.;
  INFORMAT BAR :$64.;
  INPUT ID BAR $;
RUN;

PROC SQL;
  CREATE TABLE TEST AS 
  SELECT *
  FROM FOO
  WHERE BAR IS NULL;
QUIT;

Still getting rows with blank BAR columns.

Checking to see what's in those columns I do this:

PROC SQL;
  CREATE TABLE TEST2 AS 
  SELECT
    ID,
    PUT(BAR, $HEX32.)
  FROM FOO
  WHERE BAR IS NULL;
QUIT;

And it outputs ASCII space characters.

OK, what if the original file has some spaces in it. Can I just trim it? Let's see:

DATA FOO2(KEEP=ID BAR);
  INFILE '/path/to/csv/file' DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2;
  INFORMAT ID 10.;
  INFORMAT _BAR :$64.;
  INPUT ID _BAR $;
  BAR = TRIM(_BAR);
RUN;

PROC SQL;
  CREATE TABLE TEST3 AS 
  SELECT
    ID,
    PUT(BAR, $HEX32.)
  FROM FOO2
  WHERE BAR IS NULL;
QUIT;

Nope, apparently not. Same deal. ASCII space characters. TRIM does nothing. How do I kindly tell SAS to input this file and give it appropriate values?

Re: Importing a .csv file with a blank column

tomcmacdonald — Fri, 20 Apr 2018 14:28:52 GMT

What if we were to use PRXCHANGE instead of TRIM?

DATA FOO3(KEEP=ID BAR);
  INFILE '/path/to/csv/file' DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2;
  INFORMAT ID 10.;
  INFORMAT _BAR :$64.;
  INPUT ID _BAR $;
  BAR = PRXCHANGE('s/\s+//', -1, _BAR)
RUN;

PROC SQL;
  CREATE TABLE TEST4 AS 
  SELECT
    ID,
    PUT(BAR, $HEX32.)
  FROM FOO3
  WHERE BAR IS NULL;
QUIT;

Again, it's a no go.

Re: Importing a .csv file with a blank column

Reeza — Fri, 20 Apr 2018 14:45:25 GMT

What happens with

  WHERE not missing(bar);

Re: Importing a .csv file with a blank column

tomcmacdonald — Fri, 20 Apr 2018 14:57:37 GMT

OK here's my solution

DATA FOO4(KEEP=ID BAR);
  INFILE '/path/to/csv/file' DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2;
  INFORMAT ID 10.;
  INFORMAT BAR :$64.;
  INPUT ID BAR $;
  IF NOT PRXMATCH('/^\s+$/', BAR);
RUN;

Re: Importing a .csv file with a blank column

tomcmacdonald — Fri, 20 Apr 2018 14:47:09 GMT

Same results.

Re: Importing a .csv file with a blank column

Reeza — Fri, 20 Apr 2018 14:51:03 GMT

You can also use the COMPRESS and all spaces option.

Re: Importing a .csv file with a blank column

ballardw — Fri, 20 Apr 2018 15:18:02 GMT

@tomcmacdonald wrote:

Here's what I'm trying to do:

Still getting rows with blank BAR columns.

OK, what if the original file has some spaces in it. Can I just trim it? Let's see:

Nope, apparently not. Same deal. ASCII space characters. TRIM does nothing. How do I kindly tell SAS to input this file and give it appropriate values?

Can you post a few lines of your input data file. Or create data step of your set FOO: Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Importing a .csv file with a blank column

tomcmacdonald — Fri, 20 Apr 2018 15:32:05 GMT

Here's what the CSV file looks like:

id,bar
1, 
2, 
3,Joe
4,Steve
5,Henry

Re: Importing a .csv file with a blank column

tomcmacdonald — Fri, 20 Apr 2018 15:43:18 GMT

I feel as though I'm missing something fundamental wrt SAS and whitespace. Lots of operation apparently don't work. CATT, TRIM, PRXCHANGE with whitespace regular expressions all yield whitespace and seem to do nothing. It's a bit jarring coming from a Python, R environment where this isn't even a thought.

Re: Importing a .csv file with a blank column

Reeza — Fri, 20 Apr 2018 15:46:42 GMT

SAS doesn't have a NULL value.

Re: Importing a .csv file with a blank column

tomcmacdonald — Fri, 20 Apr 2018 15:51:33 GMT

What does NULL mean in the context of PROC SQL?

Re: Importing a .csv file with a blank column

Reeza — Fri, 20 Apr 2018 16:21:50 GMT

SAS doesn't have a concept of null, regardless of SQL or Data Step.

In many RDBMS there is a difference between Null, which means nothing, and a space, whereas SAS does not recognize the difference. Both would return a missing.

EDIT: see the response from SAS employee here:

https://communities.sas.com/t5/SAS-Procedures/Missing-Null/td-p/33383

Re: Importing a .csv file with a blank column

ballardw — Fri, 20 Apr 2018 16:26:48 GMT

Try

DATA FOO;
  INFILE '/path/to/csv/file' DELIMITER = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2;
  INFORMAT ID 10.;
  INFORMAT BAR $64.;
  INPUT ID BAR $;
RUN;

Re: Importing a .csv file with a blank column

Tom — Sun, 22 Apr 2018 14:20:46 GMT

@tomcmacdonald wrote:

Here's what the CSV file looks like:
id,bar
1, 
2, 
3,Joe
4,Steve
5,Henry

So in that data the first two observations have empty values for BAR.

Is your problem that your dataset does NOT have empty value for BAR in those two cases or that you don't understand how SAS stores character variables?

Remember that SAS stores character variables as fixed length. In your original data step one side effect of the INFORMAT statement you used is that BAR will be defined as a character variable of length 64. So observations 1 and 2 should have BAR equal to 64 blanks. Observation 3 will have 'Joe' with 61 trailing blanks. In normal comparisons SAS will ignore the trailing blanks. In PROC SQL SAS will treat a value that is all blanks as NULL.

But if you store it into an external DBMS as a CHAR(64) instead of a VARCHAR(64) variable then the external DBMS might not treat it as NULL.

If the issue is that the values are not empty then probably one of these two things happened.

If you read the data WITHOUT the TRUNCOVER option on the INFILE statement (or the deprecated MISSOVER option that your posted code is using) then the first two lines would be might have been read as one observation with ID=1 and BAR='2'. Look for note to the effect that SAS went to a new line.

If your files has other non-printing characters then BAR will have something that looks blank but is not all blanks. Like a null character ('00'x) or a tab ('09'X) or a carriage return ('0D'x) or what microsoft calls a non-breaking space ('A0'x) or a DEL character ('7F'x). Or really anything with an ascii code less than a space ('20'x).

Re: Importing a .csv file with a blank column

Patrick — Mon, 23 Apr 2018 00:05:53 GMT

@tomcmacdonald

Below code returns the expected result. What are you doing differently in your code?

data foo3(keep=id bar);
  infile datalines delimiter = ',' missover dsd /*lrecl=32767*/ firstobs=2;
  INFORMAT ID 10.;
  INFORMAT BAR $64.;
  INPUT ID BAR ;
  datalines;
id,bar
1, 
2, 
3,Joe
4,Steve
5,Henry
;
run;

proc sql feedback;
  create table test4 as 
  select
    id,
    bar
  from foo3  
  where bar is not null;
quit;

Please note that using the column modifier in the INFORMAT statement isn't valid syntax. You only use this modifier on the INPUT statement to distinguish formats from same named informats. You also shouldn't use the $ in your input statement as this is going to overwrite your informat definition for BAR.

SAS doesn't have a concept of NULL but you still can use the NULL keyword in the SAS SQL flavor. SAS will then treat such a NULL the same as a missing. In SAS SQL the syntax bar is not NULL is equivalent to bar ne ' '

Re: Importing a .csv file with a blank column

Tom — Mon, 23 Apr 2018 05:22:36 GMT

You only use this modifier on the INPUT statement to distinguish formats from same named informats. You also shouldn't use the $ in your input statement as this is going to overwrite your informat definition for BAR.

The colon modifier on an INPUT statement has nothing to do with formats. It is instructions to INPUT to ignore the width of the informat and instead just read the data using list mode, even if there is an informat specification in the INPUT statement.

The $ in the INPUT statement is doing no harm, but it is also not doing any good. The meaning of the bare $ in the INPUT statement is that SAS should default the referenced variable to be character. But in that data step the both variables had already had their type determined as a side effect of appearing in an earlier INFORMAT statement.

Re: Importing a .csv file with a blank column

Patrick — Mon, 23 Apr 2018 08:51:08 GMT

@Tom

Thanks and true, I've got the explanation for the colon modifier used in the INPUT statement wrong.

Nevertheless in the code the OP posted the colon modifier has been used in the INFORMAT statement which to my astonishment didn't even generate a warning in the Log. BUT: It makes a difference to the data type created (numeric) AND using the $ in the INPUT statement in combination does make a difference as well (as now it becomes character).

Using the colon for INFORMAT BAR :$64.; actually behaves as if this statement didn't get executed at all. Play around with it and you'll see.

data foo3(keep=id bar);
  infile datalines delimiter = ',' missover dsd /*lrecl=32767*/ firstobs=2;
  INFORMAT ID 10.;
  INFORMAT BAR :$64.;
  INPUT ID BAR $;
  datalines;
id,bar
1, 
2, 
3,Joe
4,Steve
5,Henry
;
run;