BookmarkSubscribeRSS Feed
imdickson
Quartz | Level 8

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

8 REPLIES 8
Timmy2383
Lapis Lazuli | Level 10
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
Quartz | Level 8
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
Quartz | Level 8

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
SAS Super FREQ

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

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
Quartz | Level 8

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
Lapis Lazuli | Level 10

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.

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!

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.

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
  • 8 replies
  • 1285 views
  • 1 like
  • 3 in conversation