DATA Step, Macro, Functions and more

Help on SAS macro

Reply
Occasional Contributor
Posts: 19

Help on SAS macro

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

Frequent Contributor
Posts: 130

Re: Help on SAS macro

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!

Contributor
Posts: 20

Re: Help on SAS macro


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

Ask a Question
Discussion stats
  • 2 replies
  • 182 views
  • 0 likes
  • 3 in conversation