How to add a condition checking statement in stored process

[imdickson]

Im doing stored process at the moment. I have sucessfully made it and generate a report. However, what if i want to add a condition checking statement to show "no data from users side" when there is no data to generate the report rather than SAS default error? any help?

Im using SAS EG

[Timmy2383]

Could do something simple like create a macro within the code that auto-executes. Inside macro use a proc sql step to "count(*) into: obs_count from <your dataset>". Then use macro code to do a conditional statement "%if &obs_count >= 0 %then %do; <report code> %end;" then add alternate condition of no obs/records "%else %do; <print a message> %end;".

Sorry, doing this from phone.

[imdickson]

proc print data=WORK.JOIN2BCAE obs='No.' label;

var X_BCAE_NAME X_BCAE_POSITION X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW newsum;
label X_BCAE_NAME = 'Name'
X_BCAE_POSITION = 'Position'
X_BCAE_INCOME = 'Income'
X_BCAE_CARLOAN = 'Car Loan Installment'
X_BCAE_MORTGAGE = 'Mortgage'
X_BCAE_CASHFLOW = 'Cash Flow'
newsum = 'Maximum New Loan';
sum X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW newsum;
RUN
;

I have included my code....can you guide me mroe on how to do a checking to see if the WORK.JOIN2BCAE IS null or not...if null, then print 'No data found man'. Possible?

[imdickson]

I have made a very simple SQL to count observation. What i wanna do is if its 0, then PROC PRINT data=work.nodata
ELSE PROC PRINT data=work.final

is it possible?

[BrunoMueller]

Hi

Have a look at this blog entry http://bi-notes.com/2014/01/sas-stored-process-no-results-found/ by @TriciaAanderud it explains in detail how to do it.

Bruno

[Timmy2383]

There are multiple ways to do this, of course, but based on what you've provided I think it might look something like this:

%MACRO CheckIt;

PROC SQL NOPRINT;
	SELECT COUNT(*) INTO: OBS_COUNT
	FROM WORK.JOIN2BCAE;
QUIT;

%IF &OBS_COUNT. > 0 %THEN %DO;

	proc print data=WORK.FINAL obs='No.' label;

	var X_BCAE_NAME X_BCAE_POSITION X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW newsum;
	label X_BCAE_NAME = 'Name'
	X_BCAE_POSITION = 'Position'
	X_BCAE_INCOME = 'Income'
	X_BCAE_CARLOAN = 'Car Loan Installment'
	X_BCAE_MORTGAGE = 'Mortgage'
	X_BCAE_CASHFLOW = 'Cash Flow'
	newsum = 'Maximum New Loan';
	sum X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW newsum;
	RUN
	;

%END;

%ELSE %DO;

	PROC PRINT DATA=WORK.NODATA;
	RUN;

%END;

%MEND;

%CheckIt;

Or if your PROC PRINT statements would be the same for "FINAL" or "NODATA" then perhaps you could just make the dataset name dynamic, like this:

%MACRO CheckIt;

PROC SQL NOPRINT;
	SELECT COUNT(*) INTO: OBS_COUNT
	FROM WORK.JOIN2BCAE;
QUIT;

%IF &OBS_COUNT. > 0 %THEN %LET DATASET=FINAL;
	%ELSE %LET DATASET=NODATA;

	proc print data=WORK.&DATASET. obs='No.' label;

	var X_BCAE_NAME X_BCAE_POSITION X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW newsum;
	label X_BCAE_NAME = 'Name'
	X_BCAE_POSITION = 'Position'
	X_BCAE_INCOME = 'Income'
	X_BCAE_CARLOAN = 'Car Loan Installment'
	X_BCAE_MORTGAGE = 'Mortgage'
	X_BCAE_CASHFLOW = 'Cash Flow'
	newsum = 'Maximum New Loan';
	sum X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW newsum;
	RUN
	;

%MEND;

%CheckIt;

[imdickson]

what if i have other PROC statement and datastep before PROC SQL nodata?

the %MACRO should remain there or move to the beginning of the .sas file?

[imdickson]

Im getting this error when doing this code:

%MACRO CheckIt;
proc sql;
create table countv2 as
select COUNT(CASE_RK) as n 'number'
from WORK.JOIN2BCAE;
quit;

%IF %SYMEXIST n=0 %then %do;

data testdo;
set WORK.JOIN2BCAE;
/*%if countv2.n =0 %then %do;*/
proc print data=nodata;
run;
%END;
%ELSE %DO;
proc print data=WORK.JOIN2BCAE obs='No.' label;
title 'Alfred Employee Bank Commitment/Financial Status';
var X_BCAE_NAME X_BCAE_POSITION X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW newsum;
label X_BCAE_NAME = 'Name'
X_BCAE_POSITION = 'Position'
X_BCAE_INCOME = 'Income'
X_BCAE_CARLOAN = 'Car Loan Installment'
X_BCAE_MORTGAGE = 'Mortgage'
X_BCAE_CASHFLOW = 'Cash Flow'
newsum = 'Maximum New Loan';
sum X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW newsum;
RUN;
%END;
%MEND;
%CheckIt;

ERROR: Expected open parenthesis after macro function name not found.

Any advice? 

[Timmy2383]

Your symexist syntax is not valid. Is there any particular reason why you decided to create a table in the PROC SQL step and use %SYMEXIST instead of the code I suggested?

To answer your question from before, the macro invocation/execution should be in your code where it makes most sense to evaluate the condition.  Usually, though, it's going to be somewhere towards the end after you've done the prerequisite processing.