BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EinarRoed
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

' 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

3 REPLIES 3
art297
Opal | Level 21

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.

Tom
Super User Tom
Super User

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;

Patrick
Opal | Level 21

' 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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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