Check the SAS Community Library for helpful articles including several macros. Also many in SAS Global Forum papers, which are indexed thoroughly at lexjansen.com.   Some others also available at GitHub projects for SAS Global Forum.  Those from 2019 and 2020 (2021 in development) are here: https://github.com/sascommunities. ... View more

CATS() builds a string by concatenating all of its arguments (3 of them in this case). CALL EXECUTE() submits code to run after the data step.   The single quotes prevent the %report_one macro call from running while the data _null_ step is being compiled.   The %NRSTR() wrapper around the %report_one reference is to prevent the macro from running while CALL EXECUTE is pushing the call onto the stack.  In your case all it is really doing is making the SAS log easier to read since now it only shows the calls to the macro as the statements that CALL EXECUTE generated instead of showing all of the statements that the macro generates for each call.    But if the macro actually was using macro logic to make decisions on what code to run based on things that were changed by earlier SAS code that the macro generated then you have a timing problem.  Running the macro while it is being pushed onto the stack will cause the decisions on what code to generate to be made before the code that was setting the values that drive the decision would have had a chance to run. So the %NRSTR() prevents the timing issue (in addition to making the SAS log easier to read).   The FACTORY reference in the code is to the variable in the data set.  You just need a list of all of the factory workbooks you want to create.  So in this example I just used the ALL dataset your code was already using.  The BY and subsetting if will insure that each value of FACTORY is only used once to generate a macro call. ... View more

Thank you so much for all of your help! I found out the issue is case insensitive. I changed that to all uppercase like @FreelanceReinhard and it works perfect! ... View more

%let name=%sysfunc(tranwrd(&_CLIENTPROJECTNAME,&_CLIENTPROJECTPATH,)); ... View more

Thank you very much. What is the equivalent of this in Proc SQL? ... View more

Hi PhilC,   Thanks for pulling up the previous case - it was exactly what I wanted to do. The only thing is when I was trying to do the complicated sql step (I'm entry level), SAS kept giving errors although I replaced my variables accordingly. I saved the solution for future use. It was absolutely helpful!   Regards, Alicia ... View more

Define big. If you get really, really big, you may want a hash solution. Otherwise, here are two ways to find the distinct counts; https://github.com/statgeek/SAS-Tutorials/blob/master/count_distinct_by_group.sas ... View more