Macro sometimes works. Sometimes does not work

[fierceanalytics]

Here is the macro operation. After kx=(kx+12)/12, kx does have 1-4. Variables LL and k4 do not have missing values. Last Friday, when it used single quote ', it ran fine producing 8 macro variables beginning with Bal_y..... Today, it stopped creating these macro variables. I changed single quote to double quote ". Same 'not producing' problems if changing it back to '. 

I suspect there is problem with my SAS server administration... system problem. Before I call IT, does anybody see any innate programming issue in this piece? if I manually type out a line to assign one of the 8 macro variables, it works fine. But down the road I could have 64 such variables. I certainly don't want to park 64 lines in the code. Thanks

data _null;
set &um_bal (where=(kx in (0,12,24,36))); kx=(kx+12)/12;

%macro k ; %do kxy =1 %to 4;
%if kx =&kxy %then %do;
call symputx ("Bal_y&kxy._LL", LL, "G");
call symputx ("Bal_y&kxy._k4", k4, "G");
%end; %end;
%mend k; %k;
run;

[Reeza]

You should not define a macro within a data step. Keep it separate.

I think you don't need a macro here at all though.

The first parameter to call symput could be generated using CATT?

[Reeza]

data _null;
set &um_bal (where=(kx in (0,12,24,36))); 

kx=(kx+12)/12;

do kxy =1 to 4;
if kx =kxy then do;
call symputx (catt("Bal_y", kxy, "_LL"), LL, "G");
call symputx (catt("Bal_y", kxy, "_k4"), k4, "G");
end;
 end;

run;

Since you also filter kx to be 0, 12, 24, 36 it seems redundant to check if kx=kxy but I'll leave that logic for you to resolve. 

[ballardw]

The data step upon seeing your %macro statement will treat that as ending the data step. So you cannot define a macro inside a data step.

The macro language also does not see data set variables. So if this is supposed to compare the value of a variable in the data then it will never be true (likely throwing messages about %eval problems)

      %if kx =&kxy %then %do;

If the purpose of that code is to determine which position in the value list 0,12,24,36 that matches KX then SAS has a function WHICHN that does such a search and returns the position number when found.

So the data step, including @Reeza's suggestion could be reduced to

data _null;
   set &um_bal (where=(kx in (0,12,24,36))); 
   kxy= whichn(kx,0,12,24,36);
   call symputx (catt("Bal_y",kxy,"_LL"), LL, "G");
   call symputx (catt("Bal_y",kxy,"_k4"), k4, "G");
run;

However there may be something you haven't considered, since no example data was provided, that the above will have the macro variable with the LAST value of LL or K4 when KX matches the list if there is more than one occurrence.

[Quentin]

---

@ballardw wrote:

The data step upon seeing your %macro statement will treat that as ending the data step. So you cannot define a macro inside a data step.

---

Agree with your points.  You cannot define a macro inside a data step, because the the macro would be compiled before the data step is compiled.  But the %macro statement does not actually end the data step. So while it would be a bad idea, the languages do allow you to write code like:

options mprint ;
data want ;
  set sashelp.class ;
  %macro me() ;
    %put hello ;
    name2=upcase(name) ;
  %mend me ;
  %me()
  age2=age**2 ;
run ;

Not suggesting that is a good idea; it's a bad idea. : )

[Tom]

First point: The macro PRE-processor does its work and passes the resulting text of the program onto SAS itself to actually run.  So do not embed macro statements like %MACRO or %LET in the middle of SAS steps. That will just confuse the programmers looking at the source code.

Second point: Your macro variable KXY is NEVER going to equal the string kx.  KXY is being set to the strings 1, 2, 3 and 4. None of those four strings looks anything at all like the string kx.

Let's look at what code you tried to run (moving the macro code before the start of the DATA step to avoid confusion)>

%macro k ; 
%do kxy =1 %to 4;
  %if kx =&kxy %then %do;
call symputx ("Bal_y&kxy._LL", LL, "G");
call symputx ("Bal_y&kxy._k4", k4, "G");
  %end; 
%end;
%mend k; 

data _null;
  set &um_bal (where=(kx in (0,12,24,36))); 
  kx=(kx+12)/12;
%k;
run;

So you have a macro that is going to try to generate up to 8 macro variables.  You then execute that macro in a data step, which is good since CALL SYMPUTX() statements/functions are only valid inside a data step.

Are you perhaps trying to test if the macro variable KXY matches the value of the data set variable KX?  If so you need to use an actual IF statement since that will have access to the value of KX variable.

if KX=&kxy then ...

Is the purpose of 

  kx=(kx+12)/12;

To convert the values 0,12,24,36 into 1,2,3,4 respectively?

Why not just do:

kx=1+kx/12;

So perhaps you want to do this instead?

data _null;
  set &um_bal (where=(kx in (0,12,24,36)));
  kx=1+kx/12;
  call symputx(cats('bal_y',kx,'_ll',ll,'g');
  call symputx(cats('bal_y',kx,'_k4',k4,'g');
run;

So if you find observations for all four of those values of KX in UM_BAL then it will generate 8 macro variables.

If it finds less values of KX then fewer values will be generated.

Third Point:  Perhaps you want to add a step to set some default values for the 8 macro variables to cover the case when the input dataset does not have all four requested values of KX.

[fierceanalytics]

Thank you all for your replies, suggestion, explanation. This is really quality place to be with.

A bit more background

1. 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
2. So long list of macros have to be generated so references across Excel tabs become macro calling within one big piece of SAS code. 
3. 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. 

1. 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. 

[Tom · Accepted solution]

You can put macro code where ever you want. SAS will not get confused, since it is just a programming language, but you and any other person trying to figure out what the code is doing will be confused.

The macro you posed did NOTHING as the %IF statement condition being tested could NEVER be true.

So if it worked for whatever purpose you were using it for it was because those macro variables already had the values you needed for the rest of the code to work.  So it probably stopped working when those macro variables no longer had  the values you needed.  Either the values were changed by some other code, or the new situation needed different values.

Note it is certainly a lot easier to replicate data with code than with pointing and clicking at a spreadsheet.  Just define the rules for the replication and write the code.

I am not at all sure what you are trying to do, but you should take a step back and try to solve the problem using SAS coding methods.  Do not just try to replicate methods that might have working in Excel.  Different tools work differently.

[ballardw]

What does this actually mean: "Mission: trying to convert a long list of Excel tabs to SAS code."

TABS are sheets in a workbook. So converting a "sheet" to code is a problematic concept.

If you mean to "read data" that is one thing, and you are finding that Excel is a poor data interchange in general.
If you mean you are trying to convert formula or such, then say so. Quite often when moving from one 'language' environment to another it is better to analyze what one source does and then rewrite it in the other because of different features. Trying to do anything resembling "line by line" equivalency is a nightmare.

"So long list of macros have to be generated so references across Excel tabs become macro calling within one big piece of SAS code."

The need for any macro has not actually been shown. Yes your initial question was a reduction of complexity. Which means the question maybe should have been stated as "I have XYZ stuff and need to make ABC". NOT provide a non-working very small piece of a bit of the problem.

"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.:

Some blocks of what???

"4 year loop" and 0,12,24,36 means that your base "4 years" was very likely poorly done, not uncommon with spreadsheets.

"Zip up rows"????  example.

SAS provides regular expressions as well.

"Sometimes works" means "sometimes doesn't work". Which means there is usually some data exception that is not accounted for. Which spreadsheets are notorious for. Example: I inherited a work process that supposedly involved: update this data and the chart reflects the change". Then I found that the graph would not update correctly. The image would update but one of the values did not make sense (think of 4 bar charts above each other, one was supposed to be the TOTAL of the 3 individuals, but it wasn't). I spend hours going through cells individually and found where one of the cells had a "divide the value by 4" formula, which is why the Total was too small. Someone had most likely taken an ANNUAL report and decided that a Quarterly report should be divided by 4 and that code snuck in.

You repeatedly conflate MACRO and "Macro variable". They are not the same and thinking as such may be part of the issue.

Provide dummy not sensitive data that demonstrates a more complete example of the problem and the needed result.

If the "sensitive" is variable values like names or similar replace them with nonsense strings, XYZZY, or pop-culture characters, Rocky and Bullwinkle.

[Kurt_Bremser]

As already stated, your macro as posted can NEVER have worked, since the condition in the %IF can never be true, so the CALL SYMPUTX would never be written to the data step.

Your other statements show that you are trying to do something near impossible (convert the formulas of a spreadsheet one-by-one), and use the wrong tool (macro language) on top.

Take a big step back and look at the logic, then implement the logic with data step tools, or even SAS procedures.

Show us the structure of the data you start with, explain the logic, and show the result you expect.

To get tested code, post example data in a working data step with datalines.

[fierceanalytics]

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. 

[Tom]

So it sounds like you are trying to replicate THE ANALYSIS that was being done by the formulas in an excel sheet.

So you need to understand what the purpose of the formulas was.  That is don't replace =SUM(A1..A27) with some type of SAS code does those exact steps.  Instead figure out why that sum is needed how it is used in the worksheet to produce the other values in the worksheet.  Then decide how to do the same thing in SAS code.

You might find that you need to have the data organized differently to work with SAS procedures and SAS steps than you needed for transferring the data into an Excel file for the Excel logic to handle.

I can tell for a fact that the macro logic you posted could never work (at least this part). 

%do index=1 %to 4 ;
   %if &index = ABC %then %put This will never be written ;
%end;

Perhaps what you posted did not accurately reflect the actual code. Perhaps the actual code changed. Perhaps that section of the code was not actually used for anything.

[fierceanalytics]

This is a bit beyond programming. Hear me out please. 

After moving to SAS code, with all the inter-tab partitions disappeared, gliding over  a long piece of sas code (if I deck all the code up front on the driving piece, we are looking 4000 lines +) is like navigating a long snow slope in Salt Lake City valley. The distinction is obvious. With Excel, no matter how analytical we say we are, jamming at eyes and minds are data, cell, bits (with all due respect). But gliding at, and staring at SAS code, first, and foremost, the focus has to be analytical, logical first. In my case, obviously I have to place sections of datalines to source in Excel inputs, but in doing that I barely pay attention to data points (unless missing delimiters...) + now If I shrink back from SAS to Excel, I end up cutting out ~1/3 tabs. Many cross-references embedded in Excel are just so cliched redundancy because  mind is default-full with cells, cells, data points... not on cogent logic streams. So you like your Excel, by all means keep it. Just that if you have to move it to SAS, there could be intrinsic analytic benefits that you may actually like. 

[Quentin]

Glad you got it working.  I think you might be misunderstanding people's comments about translating Excel to SAS.

I don't think any of us (SAS programmers) would argue that the Excel "applications" (spreadsheets / macros/ blah blah) should not be replaced with SAS applications.

But after the decision to replace an Excel monster with something built in SAS, it probably doesn't make sense to try to literally translate cell-by-cell, formula-by-formula.  Instead, typically works better if you back up to understand and define the requirements for the Excel, and then program a SAS-native implementation.  I'm not a linguist, but I think of it like a person translating a poem from one language to another, wouldn't do it word by word with a dictionary.

But again, if you and your employer are happy with your solution, then congrats.

[Reeza]

---

@fierceanalytics wrote:

Thank you all for your replies, suggestion, explanation. This is really quality place to be with.

A bit more background

1. 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
2. So long list of macros have to be generated so references across Excel tabs become macro calling within one big piece of SAS code. 
3. 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. 

1. 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. 

---

I said not supposed to, not wouldn't work. You can program while doing a headstand but it's probably not a good idea. 

And you're not supposed to because it's bad coding style, hard to maintain, fix or understand, which does appear to be correct.