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

7 REPLIES 7
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?

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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
  • 7 replies
  • 2401 views
  • 0 likes
  • 2 in conversation