05-06-2016 09:56 AM
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.
05-06-2016 10:53 AM
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.
05-06-2016 10:16 AM
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;
05-06-2016 10:42 AM
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.
05-06-2016 10:53 AM
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.
05-06-2016 11:17 AM
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.
Need further help from the community? Please ask a new question.