Your SAS programs, embedded in web apps and elsewhere

How to add a condition checking statement in stored process

Reply
Frequent Contributor
Posts: 77

How to add a condition checking statement in stored process

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

Regular Contributor
Posts: 174

Re: How to add a condition checking statement in stored process

Posted in reply to imdickson
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.
Frequent Contributor
Posts: 77

Re: How to add a condition checking statement in stored process

Posted in reply to 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?

Frequent Contributor
Posts: 77

Re: How to add a condition checking statement in stored process

Posted in reply to 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?

SAS Super FREQ
Posts: 709

Re: How to add a condition checking statement in stored process

Posted in reply to imdickson

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

 

Regular Contributor
Posts: 174

Re: How to add a condition checking statement in stored process

[ Edited ]
Posted in reply to imdickson

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;

 

Frequent Contributor
Posts: 77

Re: How to add a condition checking statement in stored process

Posted in reply to Timmy2383

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?

Frequent Contributor
Posts: 77

Re: How to add a condition checking statement in stored process

Posted in reply to Timmy2383

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? 

Regular Contributor
Posts: 174

Re: How to add a condition checking statement in stored process

[ Edited ]
Posted in reply to imdickson

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.

Ask a Question
Discussion stats
  • 8 replies
  • 490 views
  • 1 like
  • 3 in conversation