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.
' 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
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.
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;
' 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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.