topic Help on SAS macro in SAS Programming

Help on SAS macro

KannanBaskar — Tue, 14 Jul 2015 17:36:11 GMT

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

Re: Help on SAS macro

dcruik — Tue, 14 Jul 2015 18:41:00 GMT

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!

Re: Help on SAS macro

Tommywhosc — Tue, 14 Jul 2015 19:18:22 GMT

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