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
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?
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?
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
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;
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?
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?
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.