Hi All,
I wanted to create the sales report for 1000 merchants that too it should run in the batch mode. So i am using macro facilties to avoid the coding work. But the problem some datasets does not have any observations so it is unable to create any intermidiate tables. getting error message the file table_merchant_77 does not exit and it is passing error code = 8 so my jobs will be failed.
Please can you let me know how to avoid the macro exution if dataset is having zero observations.
Please consider the code.
option nosyntaxcheck
Data merchant_1;
Input Item $ sales_amount;
Datalines;
shoe 50
fan 100
TV 500
SOFA 1000
;
run;
.
.
.
.
.
.
.
.
Data merchant_999;
Input item $ sales_amount;
Datalines;
LOCK 50
PC 100
MUG 500
LAPTOP 1000
;
run;
%macro report(seq);
ODS OUTPUT TABLE=TABLE_merchant_&seq (DROP=_TABLE_ _PAGE_);
PROC TABULATE DATA=merchant_&seq
CLASS ****;
TABLE ****;
RUN;
ODS LISTING;
ODS OUTPUT
CLOSE;
%mend report
%report(1);
%report(2);
%report(3);
%report(4);
%report(5);
%report(6);
%report(7);
%report(8);
.
.
%report(999);
You can add an observation check on the data set before you run the proc tabulate and do nothing if it's empty. Try adding the following:
%macro report(seq);
%let DSID=%sysfunc(OPEN(merchant_&seq.,IN));
%let NOBS=%sysfunc(ATTRN(&DSID,NOBS));
%let RC=%sysfunc(CLOSE(merchant_&seq));
%IF &NOBS=0 %THEN %DO:
%PUT "NO OBSERVATIONS IN MERCHANT_&SEQ DATA SET"; %END;
%ELSE %DO;
ODS OUTPUT TABLE=TABLE_merchange_&seq (DROP=_TABLE_ _PAGE_);
PROC TABULATE DATA=merchant_&seq
CLASS ****;
TABLE ****;
RUN;
ODS LISTING;
ODS OUTPUT CLOSE; %END;
%mend;
Hope that helps!
Hi Kannan,
One way is to have another macro test if a data set exists, and if so, then count the number of observations.
Then, if the conditions are met, call the macro that does the PROC.
I've taken your data and simplified it a bit (using PROC MEANS, and creating a 0 obs data set)....
Data merchant_1;
Input Item $ sales_amount;
Datalines;
shoe 50
fan 100
TV 500
SOFA 1000
;
run;
Data merchant_999;
Input item $ sales_amount;
Datalines;
LOCK 50
PC 100
MUG 500
LAPTOP 1000
;
run;
/* create data set with 0 obs...*/
data MERCHANT_2;
set MERCHANT_1;
if sales_amount < 1;
run;
%macro TAB;
proc means data=MERCHANT_&seq ;
var sales_amount;
title1 "STATISTICS from data set MERCHANT_&seq";
run;
%mend TAB;
%macro REPORT(seq);
%if %sysfunc(exist(MERCHANT_&seq)) %then %do;
%let dsid = %sysfunc(open(MERCHANT_&seq)); *open the data set ;
%let NumObs = %sysfunc(attrn(&dsid,nlobs)); *count number of logical observations ;
%let RC = %sysfunc(close(&Dsid));
%IF &NUMOBS > 0 %then
%TAB;
%ELSE
%PUT "DATA SET MERCHANT_&seq HAS 0 OBS!!";
%end;
%ELSE
%put "DATA SET MERCHANT_&seq DOES NOT EXIST!!";
%mend REPORT;
%report(1)
%report(2)
%report(3)
%report(999)
Hope this helps..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.