topic Re: Output with no records in Developers

Output with no records

MaheshPeesari — Mon, 09 Aug 2010 08:56:52 GMT

hi,

can you please tell me which global macro variable i have to use if no records are found in dataset i,e if i run stored process and it didn't fetch any data,then in output it has to display as
"NO OBSERVATIONS IN OUTPUT DATASET".
If it has observations then it has to perform its job normally.

Thanks & Regards,
SASA

Re: Output with no records

Vince_SAS — Mon, 09 Aug 2010 13:46:03 GMT

This is more of a Base SAS question. If you post a small sample of the code that results in no observations being fetched, somebody might be able to give you some advice.

Vince DelGobbo
SAS R&D

Re: Output with no records

Cynthia_sas — Mon, 09 Aug 2010 15:35:45 GMT

Hi:
I agree with Vince. This is more of a BASE SAS and SAS MACRO question. There is no "automatic" global macro variable that tell whether no records were found in a dataset. However, you could use a technique similar to the one shown in the this previous forum posting:
http://support.sas.com/forums/thread.jspa?messageID=6659ᨃ

Although the initial question was about IF statements, there is an example of finding out the number of observations in a dataset and issuing an error message if no observations are found.

Other relevant posts are these:
http://support.sas.com/forums/thread.jspa?messageID=8828≼
http://support.sas.com/forums/thread.jspa?messageID=33691莛
http://support.sas.com/forums/thread.jspa?messageID=16219㽛
http://support.sas.com/forums/thread.jspa?messageID=8249‹

Most of these previous postings show the use of "dictionary" tables to determine the number of observations in a dataset. Several previous forum postings have discussed the use of DICTIONARY.TABLES and other types of DICTIONARY files in order to access information about SAS datasets. With PROC SQL, you can use the INTO clause in order to create macro variables from the results of a particular query. Some of the postings are:
http://support.sas.com/forums/thread.jspa?messageID=14535㣇
http://support.sas.com/forums/thread.jspa?messageID=9171⏓
http://support.sas.com/forums/thread.jspa?messageID=11252⯴

Here are some user group papers that outline what the "dictionary" tables do:
http://www2.sas.com/proceedings/sugi30/070-30.pdf
http://www2.sas.com/proceedings/sugi29/237-29.pdf
http://www.codecraftersinc.com/pdf/DictionaryTablesRefCard.pdf
http://www.lexjansen.com/pharmasug/2006/tutorials/tu03.pdf
http://www.qsl.net/kd6ttl/sas/sqlutilov.pdf

Before you roll the necessary logic into a stored process, however, I recommend that you understand how the macro variables will work outside of a stored process, by creating some test programs, in order to aid in debugging your changed program (before you modify your stored process code).

cynthia

Re: Output with no records

data_null__ — Mon, 09 Aug 2010 18:33:07 GMT

Perhaps this example will be helpful. Comment the REMOVE statement to toggle obs vs no obs.

[pre]
data class;
set sashelp.class;
run;
data class;
modify class;
remove;
run;
ods pdf file='noreportexample.pdf';
title 'Title for report';
data _null_;
attrib name length=$50 label='00'x;
if eof then do;
file print ods;
put 'No Obs to report';
end;
stop;
set class(keep=name) end=eof;
run;
proc report data=class nowd list;
run;
ods pdf close;
[/pre]

Re: Output with no records

Cynthia_sas — Mon, 09 Aug 2010 19:31:32 GMT

Hi:
The DATA _NULL_ approach with the PUT statement might not work when a program is turned into a stored process, because %STPBEGIN typically "grabs" _webout and sometimes the PUT statement doesn't work in a stored process -- at least that was the recommendation in SAS 9.1.3 -- to avoid PUTs in DATA _NULL_ programs.

Typically, the way to get around this would be to put one obs in an "error" dataset -- where the obs was the error message that you want to display and then do a PROC PRINT or PROC REPORT on the error dataset. Also, if you used PROC REPORT to print the "error" message dataset, you would not need '00'x as the column header because REPORT has the NOHEADER option to suppress all headers.

cynthia

Re: Output with no records

data_null__ — Mon, 09 Aug 2010 20:32:14 GMT

My ignorance of stored processes is vast.

Do you think this, same but different, would work in a stored process?

[pre]
data class;
set sashelp.class;
run;
data class;
modify class;
remove;
run;
ods pdf file='noreportexample.pdf';
title 'Title for report';
data noreport;
if eof then do;
message = 'No Obs to report';
output;
call execute('proc report data=noreport nowd noheader; run;');
end;
stop;
set class(drop=_all_) end=eof;
run;
proc report data=class nowd list;
run;
ods pdf close;
[/pre]

Re: Output with no records

Cynthia_sas — Mon, 09 Aug 2010 22:06:54 GMT

Hi:
I don't know. I expect that it would work, if the timing of the CALL EXECUTE placed the code with PROC REPORT -after- the DATA step, but before the %STPEND; was executed.

I would probably opt for a very straightforward macro approach instead of tempting fate with CALL EXECUTE. Either using ATTRN or SELECT..INTO to get the number of obs returned by a process or query into a macro variable and then using %IF macro logic to conditionally execute the PROC REPORT with the error message or execute the other report if there are obs.

But I am very conservative in my approach to stored processes. Especially since I frequently refer to the stored process input parameters (chosen by the people who ran the stored process) in the error message. And, my rule of thumb with CALL EXECUTE is to only execute macro calls -- not send whole steps of program code within one quoted string -- because I like to let the code expansion and resolution happen in the macro word scanner.

cynthia

Re: Output with no records

MaheshPeesari — Tue, 10 Aug 2010 13:00:12 GMT

Thanks All

For u r suggestions.

Re: Output with no records

Vince_SAS — Tue, 10 Aug 2010 13:28:55 GMT

I took data _null_'s first sample code and converted it into a stored process. I tested the code on a stored process server running SAS®9.2 creating HTML, RTF or PDF. It also works with streaming or package output.

[pre]
data class;
set sashelp.class;
run;

data class;
modify class;
remove;
run;

data _null_;
if (eof) then do;
call symput('_ODSOPTIONS', "text='No Obs to report'");
end;
stop;
set class(keep=name) end=eof;
run;

%STPBEGIN;
title 'Title for report';
proc report data=class nowd list; run; quit;
%STPEND;
[/pre]

Vince DelGobbo
SAS R&D

Re: Output with no records

MaheshPeesari — Thu, 12 Aug 2010 06:28:23 GMT

Hi,

Thanks to everyone,For all your valuable inputs.

I am using the below code to generate a message if no records in the final dataset.Thanks Once again for your valuable inputs.

[pre]
data class;
set sashelp.class;
stop;
run;

data _null_;
if 0 then set class nobs=nobs;
call symput('datasetObs',compress(put(nobs,8.)));
run;

%macro try;
%put &datasetObs;
%if &datasetObs = 0 %then %do;
data _null_;
file _webout;
put '<H1>NO RECORDS FETCHED FOR VALUES PASSED</H1>';
run;
%end;
%else %do;
proc print data=class;
run;
%end;
%mend try;

options mprint mlogic symbolgen;
%try;

[/pre]
Thanks a lot...