General SAS Programming

[JCool42]

I have an existing process that uses a driver file to process multiple reports of the same type for differing companies. 

data _null_;

set REPORT_ROWS;

call symput ("Portfolio",compress("'" || Portfolio || "'"));

call symput ("Product",compress("'" || Product || "'"));

call execute('%callrpt');

run;

For instance if there are 4 companies that get this particular report, there are 4 distinct rows with company information, etc in REPORT_ROWS.  The original developer used the data step to loop through and call execute  a macro that pulls the given data for the company and produces a .xls report file via ODS.  Simple enough.  I was tasked with adding a dummy report if the data pull for the company data was 0 rows.  Given that a non readable .xls file was created when there was no extracted data.   I used %sysfunc to open and get the number of observations

%let dsid = %sysfunc(open(report_data));

%let rrows =%sysfunc(attrn(&dsid., nobs));

%if (&rrows. eq 0) %then %do;

proc print data=Text noobs;

run;

%end;

%else %if(&rrows. ne 0) %then %do;

proc print data=report_data noobs label split='*';

run;

%end;

I cant for the life of me figure out why (&rrows ne 0) always resolves to TRUE when I know I have (3) iterations of report_data   70 rows, 0 rows, 134 rows.

[Astounding · Accepted solution]

I thought about it a bit more, and found an easy workaround (at least it probably works).  Instead of using CALL EXECUTE, write the statements to a file and %include the file.  For example this could replace your current DATA _NULL_ step:

data _null_;

set report_rows;

file '/some/file/prog.txt' noprint;

product = compress(product);

portfolio = compress(portfolio);

put '%let product=''' product  +(-1) ''';';

put '%let portfolio=''' portfolio +(-1) ''';';

put '%callrpt';

run;

%include '/some/file/prog.txt';

It's unclear to me why you need quotes around the values of &PRODUCT and &PORTFOLIO, but that's what the original program was doing so I added them here.

Good luck.

[RW9]

Hi,

Its likely because macro variables are text not numbers.  I can't test as leaving now, but try:

options mlogic mprint symbolgen;
%macro tmp;
%if (%sysfunc(attrn(%sysfunc(open(sashelp.class)), nobs)) eq 0) %then %do;
  proc print data=sashelp.class noobs;
  run;
%end;
%else %do;
  proc print data=sashelp.cars;
  run;
%end;

%mend tmp;
%tmp;

[Astounding]

This may be the issue (or it may be an additional issue that you hadn't considered).  The statements execute in a different order than you are expecting. When this statement is encountered:

call execute('%callrpt');

The macro %CALLRPT begins to execute right away without waiting for the DATA _NULL_ step to end.  However, as it generates PROC PRINT steps, those steps cannot run yet.  Your program is in the middle of executing a DATA step, so the PROC PRINTs have to wait.  They stack up and execute once the DATA step is complete.  Since you are hard-coding names of the data sets to print, it is only the FINAL version of TEXT and the FINAL version of REPORT_DATA that get printed, over and over again.

There are ways to overcome this issue using %NRSTR, to delay the execution of %CALLRPT until after the DATA step is over.  However, those methods won't be a solution in your case because you need your macro variables &PORTFOLIO and &PRODUCT to change for each execution of %CALLRPT.  We would need to know more about the contents of %CALLRPT to suggest a solution.

Good luck.

[Astounding · Accepted solution]

I thought about it a bit more, and found an easy workaround (at least it probably works).  Instead of using CALL EXECUTE, write the statements to a file and %include the file.  For example this could replace your current DATA _NULL_ step:

data _null_;

set report_rows;

file '/some/file/prog.txt' noprint;

product = compress(product);

portfolio = compress(portfolio);

put '%let product=''' product  +(-1) ''';';

put '%let portfolio=''' portfolio +(-1) ''';';

put '%callrpt';

run;

%include '/some/file/prog.txt';

It's unclear to me why you need quotes around the values of &PRODUCT and &PORTFOLIO, but that's what the original program was doing so I added them here.

Good luck.

[JCool42]

yeah... I went back to check based on your original explanation.  I modified the dsn that is being opened (to check obs) to include a macro variable so that the name would changed based on the report.  But I like your new method better.  Might "build" a bunch of code when there are 30+ portfolios but I am confident it will work.  Will let you know...   Thanks for the quick ideas.