SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

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

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.


Accepted Solutions
Solution
‎11-05-2013 06:42 PM
Respected Advisor
Posts: 4,173

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

Posted in reply to EinarRoed

' 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

View solution in original post


All Replies
PROC Star
Posts: 7,492

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

Posted in reply to EinarRoed

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.

Super User
Super User
Posts: 7,076

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

Posted in reply to EinarRoed

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;

Solution
‎11-05-2013 06:42 PM
Respected Advisor
Posts: 4,173

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

Posted in reply to EinarRoed

' 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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 559 views
  • 6 likes
  • 4 in conversation