Thank you.    First, it is business decision to go ahead using SAS to replace Excel, one by one, tab by tab. Your opinion or my opinion does not count. I actually believe it is great direction to go at this moment. The numbers match with Excel outcome, with several hundred SAS macro variables. It has cost too much time/effort, some embarrassment on me albeit.     I concur that back in, say, 2002, perhaps using SAS to replace Excel was generally wrong/bad idea. This is 2022. SAS is much better, more open to work TO/FROM other packages. I used to use DDE, for example, to work with SAS, <Y2K. Now this sounds such a long bygone.    Second, I wish I have the liberty to disclose the log and HTML  details that show my now notorious original post worked, for a while of 2 days. Not to start debate, but your NEVER statement does have exceptions, practically. As wisdom goes, never say NEVER.    A bit more background, as I am allowed to: we run long SAS prep code (long-running jobs) that pulls, cleans, organizes the data fed into the spreadsheets. Years ago we  decided to continue the subsequent analysis using Excel not SAS, half a dozen reasons I recall. Today is time of WFH. Excel is not working as it used to in this new, remote setting. Clipboard, for example, is constantly under pressure to work properly,  thanks partially to corporate security.... Many linked formula sometimes automatically update, sometimes this-some automatically that-some don't,, vice versa. We spend a lot manpower to check, double-check, just to avoid misjudging people's quality of work, attitude....   So we return to the glory of SAS. If the users are all SAS savvy, I could live with my rough version one as is. To accommodate not-that-savvy users, I need to polish up quick a bit. One big  benefit as I see at this moment is SAS output is so much closer to Excel-readable, especially if using SAS Studio.      ... View more

Thank you all for your replies, suggestion, explanation. This is really quality place to be with.   A bit more background Mission: trying to convert a long list of Excel tabs to SAS code. The spreadsheet has tricky business logic that recently starts to behave unstably .. So there is no data throughput challenge So long list of macros have to be generated so references across Excel tabs become macro calling within one big piece of SAS code.  Initially, the code runs  as some here have suggested (no do loop whatsoever) " data _null_; call symputx(cats('bal_y1',kx,'_ll',ll,'eg'); call symputx(cats('bal_y2',kx,'_k4',k4,'g'); ....; run; "   Some blocks like this have 64 rows. Some 120.... The example I provided was 4 year loop, greatly simplified. Naturally, after the   primitive coding allowed the SAS results to have matched with Excel figures, need to zip up the rows to make it, among other things, more readable. Primitive was not hard by the way; one can use Excel easily to populate hundreds of rows of names due to their regular expressions.    Intriguing, though,  (not that I don't accept the argument "you are not supposed to create macro inside data step like this..."), is that (not insisting), the (now notorious) original piece as posted ran fine Thursday and Friday, and stopped >=Saturday 6/11th. "data _null; set &um_bal (where=(kx in (0,12,24,36))); kx=(kx+12)/12; %macro k ; %do kxy =1 %to 4; ..... %mend k; %k;  run;  "         as if it was the piece Reeza wrote and replied. I understand all the explanations ... If the original piece was so outrageous "not supposed to", why it ran for two days without warning (>50 times) resulting in correct calculation.... And NOW it simply said in the log the macro variable is not resolved.  5 . Each set of macro variables created block by block have to be 1. global. 2. have to stay with the data step ( well, point well taken that WITH the data step means NOT to put WITHIN the data step, although Thursday, Friday's success told me "well, I can. sometimes"). So I need to do some re-planning. I understand some suggestions because I used to build many macros, put them together in one big piece. Use one %include. Then call as I want, very floating,  portable. Unfortunately, in this context, THE macro has to sit with the step. (OK, not WITHIN, agreed) , due to requirement embedded with target Excel spreadsheet tabs.... or at least not floating portable manners 6. No, not trying to check if 0, 12, 24,36 are in the data set. Not a matching exercise. + I am afraid I cannot provide more details, confidentiality..... I already spent a lot of time purging  my original posted piece, but I think the original piece was sufficient for Q & A. Greatly appreciate. Thanks.    ... View more

Thank you. ... View more

Thank you. Tom. The Where -IF subject I think it clear. I am going to snippet your reply below "Note that just referencing a macro variable inside a macro definition will not necessarily make it a local macro variable. The only macro variable in your posted code is i. If there exists a macro variable named i before CVN77 is called then the %DO loop would modify the value of that existing macro variable instead of creating on the CVN77 macro scope. The macro variable i would be created local to CVN77 only if no existing macro variable named i was found at the time the %DO loop started to execute." to answer another question just asked by somebody. I habitually don't use same i, j.... crossing my macros. Somebody just asked why she cannot just use I. ... View more

Good. Thanks ... View more

Thank you.    The next question is : how to regulate length of the new variable LOC? if first pass-through is Boston, LOC gets 6? That works for Huston or Dallas fully, but NY or SF may get truncated? I rephrased my question the second time. You probably sensed this is a do-loop context from my first time of asking with that i and J typo. Thanks. Jia ... View more

Thank. Let us forget the do loop. I vs. j was just typo. %let name=boston; How to load &name to a new variable called LOC, inside proc sql; alongside getting max, mean… regular sql stuff? Thanks ... View more