Help using Base SAS procedures

Write message to .csv file if no data in output

Reply
Contributor
Posts: 33

Write message to .csv file if no data in output

I have a DI studio job, the final step of which uses the 'File Write' task to output the final data set to an external .csv file. This works fine. However, what I would like to do is insert some text into the file should there be no data in the output, rather than ujust supply the user with an empty file, e.g. 'There was no data to display'.

Can anyone suggest how this could be done?

Below is the code generated by the task:

data _null_;
set &SYSLAST;
attrib FIN_YEAR length = 8;
attrib DDV_C_MONTH length = 8
format = DATE7.;
attrib DDV_C_MON length = $1;
attrib DDV_C_CAT1_DRVS_HRS_COMBI length = 8;
attrib DDV_TOTAL_TARGET length = 8;
quote='"';
file '/home/sasdev/sas_files/test/test2.csv' dlm=',';

put
quote +(-1) FIN_YEAR +(-1) quote
quote +(-1) DDV_C_MONTH +(-1) quote
quote +(-1) DDV_C_MON +(-1) quote
quote +(-1) DDV_C_CAT1_DRVS_HRS_COMBI +(-1) quote
quote +(-1) DDV_TOTAL_TARGET +(-1) quote
;

run;

%rcSet(&syserr);
Frequent Contributor
Posts: 106

Re: Write message to .csv file if no data in output

Two things: placement of the 'file' statement and usage of option NOBS= on the set statement. Experiment with the following code by setting OBS= to 0 and 1 respectively.
[pre]
data for_test ;
set sashelp.class(obs=0); /* obs=0 for an empty dataset */
run ;
filename csvout temp ;
data _null_ ;
file csvout ;
if nobs=0 then
put "%sysfunc(datetime(),dtwkdatx.) "
+1 "%sysfunc(time(),time8.) No data found." ;
set &syslast nobs=nobs ;
format _char_ $quote. ;
put name (sex age) ( +(-1) ',') ;
run ;
data _null_ ;
infile csvout ;
input;
put _infile_ ;
run ;
filename csvout clear ;
[/pre]
Contributor
Posts: 33

Re: Write message to .csv file if no data in output

Thanks for such a speedy reply. Bear with me because I do not have much experience in writing code so am a little lost as to how to test this. I understand the obs= bit but where and how should I insert my file to test the .csv output. I have pasted the code into a node within EG to test and am only able to see the sas table output (WORK.FOR_TEST). I want to write to the following file:

'/home/sasdev/sas_files/test/test2.csv'

Thanks again for your assistance
Frequent Contributor
Posts: 106

Re: Write message to .csv file if no data in output

Don't waste your time experimenting with my code sample.

Just move your file statement immediately after the "data _null_" statement and add option "nobs=nobs" to the statement "set &syslast". Then add the "if nobs=0" statement right after the "file" statement.

Quick and dirty explanation:
1) the NOBS= option is evaluated at data step compile time
So the number of observations is known when the data _null_ step starts executing.
2) putting FILE and the test for the number of observations ("if nobs=0") ahead of the SET statement is crucial as the data step stops execution immediately upon reaching the SET statement for the last (or only) observation.

Hope this helps. If not ... go on asking ;-)
Respected Advisor
Posts: 3,777

Re: Write message to .csv file if no data in output

Many programmers think the answer to this question involves finding the number of OBS in a data set. The number of OBS is irrelevant and depending on how you determine the number possibly wrong. You just need to detect "immediate end-of-file". Can you edit the generated code? If so a simple modification will suffice. Move the FILE statement up to the top and add a line to detect EOF before the set statement is executed.

[pre]
*** Test data SYSLAST with 0 obs;
data test;
attrib FIN_YEAR length = 8;
attrib DDV_C_MONTH length = 8
format = DATE7.;
attrib DDV_C_MON length = $1;
attrib DDV_C_CAT1_DRVS_HRS_COMBI length = 8;
attrib DDV_TOTAL_TARGET length = 8;
stop;
call missing(of _all_);
run;

data _null_;
file log /*'/home/sasdev/sas_files/test/test2.csv'*/ dlm=','; *moved;

if _n_ eq 1 and eof then do; *added;
put "No obs in &syslast";
stop; *will work without this but the intent is clearer;
end;

set &SYSLAST end=eof; *modified here;
attrib FIN_YEAR length = 8;
attrib DDV_C_MONTH length = 8
format = DATE7.;
attrib DDV_C_MON length = $1;
attrib DDV_C_CAT1_DRVS_HRS_COMBI length = 8;
attrib DDV_TOTAL_TARGET length = 8;
quote='"';

put
quote +(-1) FIN_YEAR +(-1) quote
quote +(-1) DDV_C_MONTH +(-1) quote
quote +(-1) DDV_C_MON +(-1) quote
quote +(-1) DDV_C_CAT1_DRVS_HRS_COMBI +(-1) quote
quote +(-1) DDV_TOTAL_TARGET +(-1) quote
;

run;
[/pre]


If you still need to let SAS generate the code each time, if the data set is different then perhaps you could add this bit of code after the generated code.

You would of course change 'TEST2.CSV' to the correct complete file path.

[pre]
data _null_;
file 'TEST2.CSV' mod; *mod is important;
if _n_ eq 1 and eof then put "No obs in &syslast";
stop;
set &syslast end=eof;
run;
[/pre]
Respected Advisor
Posts: 3,893

Re: Write message to .csv file if no data in output

data _null_

Once more you're an inspiration!
Contributor
Posts: 33

Re: Write message to .csv file if no data in output

excellent, this seems to be working.....many thanks all
Ask a Question
Discussion stats
  • 6 replies
  • 212 views
  • 0 likes
  • 4 in conversation