BookmarkSubscribeRSS Feed
Cullima
Calcite | Level 5
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);
6 REPLIES 6
Robert_Bardos
Fluorite | Level 6
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]
Cullima
Calcite | Level 5
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
Robert_Bardos
Fluorite | Level 6
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 😉
data_null__
Jade | Level 19
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]
Patrick
Opal | Level 21
data _null_

Once more you're an inspiration!
Cullima
Calcite | Level 5
excellent, this seems to be working.....many thanks all

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1172 views
  • 0 likes
  • 4 in conversation