SAS Macro Problem

[cougar300]

I don't understand what is happening.  I'm no expert at the macro language, but I just don't understand what SAS is doing.

I have a list of brands in a macro variable name1-name13 and am iterating through the list to see if the variable brand is equal to the brand that is being iterated on.

%macro doit;
%do i=1 %to &NObs;
 %put # # # Processing &&Name&i # # #;

 %if brand = &&Name&i %then newvar=0 ; (this will actually do something else - this is just to test the comparison)

 %end;
%mend;
%doit;


Log file:

MLOGIC(DOIT):  Beginning execution.
MLOGIC(DOIT):  %DO loop beginning; index variable I; start value is 1; stop value is 13; by value is 1.  
MLOGIC(DOIT):  %PUT # # # Processing &&Name&i # # #
# # # Processing Brand A # # #
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
       brand = &&Name&i
ERROR: The macro DOIT will stop executing.

Why is &&Name&i resolving fine for the put statement, but not for the if then comparison line?

Thanks

[PaigeMiller]

%if &brand = &&Name&i %then %let newvar=0 ; 

If you are doing macro variable comparisons, then you need &BRAND. You also need %let.

[cougar300]

sorry if I didn't explain things clearly.

I have a variable brand - it is not a macro variable.

Essentially, I have a dataset that has a variable brand, then I have a variable named after each brand, and let's say I'm just trying to flag where the brand matches the variable (it's not that simple, but those are the cases in which I want to do something), so that the data I'm trying to get to is this (assuming the Brand variables are all zero to start):

BRAND     BRAND A     BRAND B     BRAND C

BRAND A       1                    0                  0

BRAND B       0                    1                  0

BRAND C       0                    0                  1

[PeterClemmensen]

In this scenario, do you have the Brand_A - Brand_C variables from the start or should they be created from the values of the variable Brand?

[PaigeMiller]

---

@cougar300 wrote:

sorry if I didn't explain things clearly.

I have a variable brand - it is not a macro variable.

Essentially, I have a dataset that has a variable brand, then I have a variable named after each brand, and let's say I'm just trying to flag where the brand matches the variable (it's not that simple, but those are the cases in which I want to do something), so that the data I'm trying to get to is this (assuming the Brand variables are all zero to start):

BRAND     BRAND A     BRAND B     BRAND C

BRAND A       1                    0                  0

BRAND B       0                    1                  0

BRAND C       0                    0                  1

---

Maybe you should show us the full log so we can see all the code and all the warnings and error messages. It seems as if you are talking about using a macro to set the value of a data set variable, but you haven't shown us any data step code.

[cougar300]

It's not complicated code, it's just getting the macro variables to resolve properly that is the problem.

The only line that needs to change is the if then line.  I started without the real THEN part because I can't get the IF comparison to work, but I'm sure the second part will be a problem as well.

Data notes: There are spaces in the brand names, so the variable names have spaces in them.  This works:

if brand = 'A/O Stuff' then 'A/O Stuff'n =1; so I'm wondering if the THEN part will need to be "&&Name&i."n or some such, but I can't get past the IF evaluating correctly at this point.

data dataset8;
set dataset7;

proc sql noprint;
select count(*)
into :NObs
from brandlist;
select brand
into :Name1-:Name%left(&NObs)
from brandlist
quit;
%macro doit;
%do i=1 %to &NObs;
%put # # # Processing &&Name&i # # #;

%if brand = &&Name&i %then &&Name&i = 1 ;
%end;
%mend;
%doit;

run;

log:

26 proc sql noprint;

27 select count(*)
28 into :NObs
29 from brandlist/*SAShelp.class*/;
30 select brand
31 into :Name1-:Name%left(&NObs)
MLOGIC(LEFT): Beginning execution.
MLOGIC(LEFT): This macro was compiled from the autocall file /ix1521/SAS94M5/sashome/SASFoundation/9.4/sasautos/left.sas
MLOGIC(LEFT): Parameter TEXT has value 13
MLOGIC(LEFT): %LOCAL I
2 The SAS System 09:30 Thursday, April 2, 2020

MLOGIC(LEFT): %IF condition %length(&text)=0 is FALSE
MLOGIC(LEFT): %LET (variable name is I)
MLOGIC(VERIFY): Beginning execution.
MLOGIC(VERIFY): This macro was compiled from the autocall file /ix1521/SAS94M5/sashome/SASFoundation/9.4/sasautos/verify.sas
MLOGIC(VERIFY): Parameter TEXT has value 13
MLOGIC(VERIFY): Parameter TARGET has value

MLOGIC(VERIFY): %LOCAL I
MLOGIC(VERIFY): %IF condition %length(&text)=0 OR %length(&target)=0 is FALSE
MLOGIC(VERIFY): %DO loop beginning; index variable I; start value is 1; stop value is 2; by value is 1.
MLOGIC(VERIFY): %IF condition NOT %index(&target,%qsubstr(&text,&i,1)) is TRUE
MLOGIC(VERIFY): %GOTO VERFND (label resolves to VERFND).
MLOGIC(VERIFY): %IF condition &i>%length(&text) is FALSE
MLOGIC(VERIFY): Ending execution.
MLOGIC(LEFT): %IF condition &i is TRUE
MPRINT(LEFT): Name13
MLOGIC(LEFT): Ending execution.
32 from brandlist/*SAShelp.class*/;
33 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 5429.18k
OS Memory 26024.00k
Timestamp 04/02/2020 11:25:52 AM
Step Count 115 Switch Count 0
Page Faults 0
Page Reclaims 15
Page Swaps 0
Voluntary Context Switches 3
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

34 %macro doit;
35 %do i=1 %to &NObs;
36 %put # # # Processing &&Name&i # # #;
37
38 %if brand = &&Name&i %then %let newvar=0 ;
39 
40 %end;
41 %mend;
42 %doit;
MLOGIC(DOIT): Beginning execution.
MLOGIC(DOIT): %DO loop beginning; index variable I; start value is 1; stop value is 13; by value is 1.
MLOGIC(DOIT): %PUT # # # Processing &&Name&i # # #
# # # Processing A/O Stuff # # #
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
brand = &&Name&i
ERROR: The macro DOIT will stop executing.
MLOGIC(DOIT): Ending execution.

[Tom]

Your code is mixed up. You have a data step that does nothing other than duplicate the data. Then an SQL step and the macro code that is not generating any valid SAS code at all.

Let's clean up the SQL step first.

proc sql noprint;
  select distinct quote(trim(brand)),nliteral(brand)
    into :brands1-,:names1-
    from brandlist
  ;
%let nobs=&sqlobs;
quit;

Now we can use those macro variables to generate SAS code (let's assume you have wrapped it into a macro definition so we can use macro logic).

data want ;
  set have;
%do index=1 %to &nobs;
  &&names&index = (brand = &&brands&index);
%end;
run;

If the values are really messy then don't use them for variable names. Just use numbers and put the values into the label of the variable.

proc sql noprint;
  select distinct quote(trim(brand))
    into :brands1-
    from brandlist
  ;
%let nobs=&sqlobs;
quit;
data want ;
  set have;
%do index=1 %to &nobs;
   brand&index = (brand = &&brands&index);
   label brand&index = &&brands&index ;
%end;
run;

[Kurt_Bremser]

Your code is not only overly complicated, it can NEVER work.

You try to run a proc sql step inside a data step; this is not possible, the data step ends as soon as SAS sees the keyword PROC. This is very basic SAS knowledge, and since you do not have that yet, you should not waste your brain cycles with macro coding. Macro coding needs solid, in-depth knowledge of the SAS language to be of any use.

Once again: post your example data in usable form, and what you want to get out of it. A possible solution without any macro coding has already been posted.

[Kurt_Bremser]

Using data step variables in macro conditions DOES NOT WORK. The macro condition is resolved before any data step code is compiled and later executed.

There is a 90%+ chance that you do not need any macro processing for what you want to do.

Having a variable for each brand value is bad data design; transpose to a long format, and your task will be MUCH easier.

To get more help, post example data (your base and/or lookup datasets), in usable form (data step(s) with datalines), and the result you want to get out of it.

[cougar300]

Oh, and to the other question, the brand variables are already there with 0 in all entries.  I don't need to create these variables, that's already been done in an earlier step.

[Kurt_Bremser]

---

@cougar300 wrote:

Oh, and to the other question, the brand variables are already there with 0 in all entries.  I don't need to create these variables, that's already been done in an earlier step.

---

Then you probably went haring off in the wrong direction there. It's a common problem we see with SAS newbies here who still think in Excel spreadsheets. Data (company names) does not belong in structure (variable names).

[Tom]

Sounds like you have this data:

data have;
  input id brand $20.;
cards;
1 Brand A
2 Brand B
3 Brand C
;

And you want to run this code:

data want;
  set have;
  brand_a = brand='Brand A';
  brand_b = brand='Brand B';
  brand_c = brand='Brand C';
run;

You can play around with figuring out how to get macro code to generate that SAS code.

But perhaps it is easier to just use SAS code instead of monkeying around with macro code.

data step1;
  set have;
  dummy=1;
run;
proc transpose data=step1 out=step2(drop=_name_);
  by id brand;
  id brand;
  var dummy;
run;
proc stdize data=step2 out=want reponly missing=0;
run;
proc print data=want;
run;

Obs    id     brand     Brand_A    Brand_B    Brand_C

 1      1    Brand A       1          0          0
 2      2    Brand B       0          1          0
 3      3    Brand C       0          0          1

[ballardw]

%IF <data set varaible name> is almost never going to do what you want.
First you must have the macro called inside a data step or procedure that defines the data set with the variables (nothing related to this shown in your example)

Second the Macro elements would process before the data step or procedure attempts to execute and would create code lines, but not comparing the variable.

If you are attempting to compare a variable in a data step to a macro variable then assign a value to a different data step variable the code would would look like:

if var =&macrovar. then newvar = 0;

If the value is a character value then you would place the macro variable inside double quotes:

if var = "&macrovar." then newvar =0;

Options MPRINT may be more useful in this case then Mlogic.

[cougar300]

These have all been helpful replies, but maybe I'm getting too far into the weeds.  What I was asking was one piece of how I was going to solve this.

Let me back up a step.

I want to start with the data in yellow and have it end up adding the data in blue as far as SAS goes, where the number in blue is the ratio multiplied by the variable to flip for the associated brand.  In Excel you'd flip the variable to the top then just multiply across, but it's not that simple in sas.  Maybe some SQL step could accomplish this, but I don't use SQL a lot either.

BIG NOTE: This is data for one store, where I'm doing it for many stores

My plan was to create another var for each Brand where Brand A FLIP would be the green area (although creating that number may not be as easy as I was initially thinking, either).

So that to get the blue area, all I would need to do would be multiply the Green Column by the ratio variable.

Maybe I was headed down the wrong path with the initial methodology to begin with as far as process goes.

So let me change the question to - can I get from the yellow area to adding the blue area in the sas dataset without too much trouble?  Easiest method?

[Tom]

Photographs of data are impossible to copy and paste into code.

If you want to combine N brands with M ratios then keep them in SEPARATE datasets.  Then combine them.

For example if the source datasets are BRAND_LIST and RATIO_LIST then code would look like this:

proc sql ;
create table want as 
select a.brand
     , b.ratio 
     , a.value * b.ratio as WANT
from brand_list a
   , ratio_list b
order by 1,2
;
quit;

If you want to make a REPORT that looks like your photograph then us PROC REPORT or PROC TABULATE.