BookmarkSubscribeRSS Feed
MaheshPeesari
Calcite | Level 5
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
9 REPLIES 9
Vince_SAS
Rhodochrosite | Level 12
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
Cynthia_sas
SAS Super FREQ
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
data_null__
Jade | Level 19
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]
Cynthia_sas
SAS Super FREQ
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
data_null__
Jade | Level 19
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]
Cynthia_sas
SAS Super FREQ
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
Vince_SAS
Rhodochrosite | Level 12
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
MaheshPeesari
Calcite | Level 5
Thanks All

For u r suggestions.
MaheshPeesari
Calcite | Level 5
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...

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 5383 views
  • 1 like
  • 4 in conversation