@andreas_lds wrote:
select(year(appln_filing_date));
%do year = &minYear. %to &maxYear.;
when (&year) output work.want_&year.;
%end;
Nice use of the select statement. 👍
Regarding the use of:
proc sql noprint;
select min(year(appln_filing_date)), max(year(appln_filing_date))
into :minYear trimmed, :maxYear trimmed
from work.have;
quit;
that's going to depend a bit on the business need, at least that's how it works in my company. Our database has many years in it, but we typically only want the current year + the prior three years. So, is it better to use SQL to detect which years are present or is it better to build the macro with years as arguments like the below?
%Gen_Dataset_Names(1986, 2020);
Well, as say, "it depends." 🙂
Now, as for the CALL EXECUTEs that you coded, even if it's not the fastest here, it's still a valuable technique to be aware of -- launching multiple steps from within a single Data step. CALL EXECUTEs are run serially, i.e. one after the other. Another option, instead of CALL EXECUTE is RSUBMIT (or SYSTASK) which launches processes in parallel, i.e. concurrently. Parallel processing, depending on the requirements, can significantly speed overall processing.
Jim
... View more