topic Re: Checking for null values in the first row of a CSV-file in SAS Data Management

Checking for null values in the first row of a CSV-file

EinarRoed — Tue, 05 Nov 2013 14:44:32 GMT

I'd like to set up a user written transformation which reads a CSV-file and sends me an email IF the first row in the CSV-file contains NULL-values. The code for sending the email is ready, but I'd appreciate help with the rest, because the way I'm doing it seems very convoluted.

Can someone please suggest the most "short and sweet" SAS code for simply reading the first row of a CSV-file and checking if it contains NULL-values?

Thanks.

Re: Checking for null values in the first row of a CSV-file

art297 — Tue, 05 Nov 2013 14:58:45 GMT

Depends upon what you refer to as null values and how your file is structured. If each of your input records capture all of the information for one person/item/date or whatever they represent, and you simply want to discover if there are any characters in that record besides commas and spaces, you could simply include a "if _n_ eq 1 then input @;" statement, compress out all of the commas and blanks from _infile_, and then check the length of the stripped _infile_ variable.

Re: Checking for null values in the first row of a CSV-file

Tom — Tue, 05 Nov 2013 15:14:16 GMT

Not sure what you mean as the first row of a CSV file is normally interpreted as the column names.

You can use the COUNTW() function with and without the M modifier to check if there are empty values. But that would treat a value that is all spaces as NOT empty.

data _null_;

input @;

n1 = countw(_infile_,',','MQ');

n2 = countw(_infile_,',','Q');

put n1= n2= +1 _infile_;

cards;

1,2,3

1,,3

1, ,3

run;

Re: Checking for null values in the first row of a CSV-file

Patrick — Tue, 05 Nov 2013 23:42:54 GMT

' first row in the CSV-file contains NULL-values'

Is that a totally empty row? So 0 to n blanks and a end-of-line indicator? Or do you have the field delimiters in it but all values are "NULL"?

Below untested code providing a sample of how you could do it for either of the 2 cases:

%let Empty_Line_Flag=0;

data _null_;

infile <yourfile>;

input;

/* only 0 to n blanks */

if missing(_infile_) then call symputx('Empty_Line_Flag','1');

/* only delimiters and 0 to n blanks */

if prxmatch('/[^, ]/oi',_infile_) = 0 then call symputx('Empty_Line_Flag','1');

/* stop: so only reading first line */

stop;

run;

... and then send an email if &Empty_Line_Flag=1