BookmarkSubscribeRSS Feed
KannanBaskar
Calcite | Level 5

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);

2 REPLIES 2
dcruik
Lapis Lazuli | Level 10

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!

Tommywhosc
Obsidian | Level 7


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..

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