topic Re: SAS Macro Problem in SAS Programming

SAS Macro Problem

cougar300 — Thu, 02 Apr 2020 15:16:59 GMT

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

Re: SAS Macro Problem

PaigeMiller — Thu, 02 Apr 2020 15:23:03 GMT

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

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

Re: SAS Macro Problem

cougar300 — Thu, 02 Apr 2020 15:29:57 GMT

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

Re: SAS Macro Problem

PeterClemmensen — Thu, 02 Apr 2020 15:39:17 GMT

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?

Re: SAS Macro Problem

PaigeMiller — Thu, 02 Apr 2020 15:43:09 GMT

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

Re: SAS Macro Problem

Kurt_Bremser — Thu, 02 Apr 2020 15:48:01 GMT

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.

Re: SAS Macro Problem

cougar300 — Thu, 02 Apr 2020 15:51:18 GMT

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.

Re: SAS Macro Problem

cougar300 — Thu, 02 Apr 2020 15:52:28 GMT

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.

Re: SAS Macro Problem

Tom — Thu, 02 Apr 2020 15:52:34 GMT

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

Re: SAS Macro Problem

ballardw — Thu, 02 Apr 2020 15:52:59 GMT

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

Re: SAS Macro Problem

Tom — Thu, 02 Apr 2020 16:07:52 GMT

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;

Re: SAS Macro Problem

Kurt_Bremser — Thu, 02 Apr 2020 16:07:56 GMT

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.

Re: SAS Macro Problem

Kurt_Bremser — Thu, 02 Apr 2020 16:11:32 GMT

@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).

Re: SAS Macro Problem

cougar300 — Thu, 02 Apr 2020 16:33:01 GMT

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?

Re: SAS Macro Problem

FreelanceReinh — Thu, 02 Apr 2020 17:09:59 GMT

@cougar300 wrote:

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

Hi @cougar300,

While the main problems with your code have been pointed out already, let me just answer the above question: In the case of &&Name&i resolving to A/O Stuff the reason was that the macro processor interpreted the slash in "A/O Stuff" as the division operator, which is valid only with numeric operands, not with character operands such as A and O. This is what the error message "A character operand was found in the %EVAL function or %IF condition where a numeric operand is required." referred to. (This would not have occurred with &&Name&i resolving to Brand A, which is what your initial post suggested.) It is indeed somewhat confusing that the error message ("The condition was: brand = &&Name&i") does not resolve the macro variable reference and thus hides the culprit (the slash in this case).

If the comparison made sense in an %IF condition, double quotes or macro quoting functions (such as %BQUOTE) would need to be applied in order to avoid the wrong interpretation of the slash.

Re: SAS Macro Problem

Tom — Thu, 02 Apr 2020 17:38:01 GMT

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.

Re: SAS Macro Problem

ballardw — Thu, 02 Apr 2020 17:38:14 GMT

Data not pictures. I'm not going to create data from pictures to write code.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

Re: SAS Macro Problem

cougar300 — Thu, 02 Apr 2020 17:54:44 GMT

Thank you

the "/" making it attempting a numeric conversion makes sense. That clarifies that part, at least.

Re: SAS Macro Problem

cougar300 — Thu, 02 Apr 2020 18:09:42 GMT

If anyone can easily turn this starting data into the blue part shown in the above picture, please let me know.

thanks

data WORK.start;
infile datalines dsd truncover delimiter=',';
input Brand:$8. Amount:32. Ratio:32.;
datalines;
Brand A,20,.4
Brand B,16,.5
Brand C,12,.25
Brand D,8,.25
Brand E,14,0
;

Re: SAS Macro Problem

Tom — Thu, 02 Apr 2020 18:34:58 GMT

Like this:

data WORK.start;
  infile datalines dsd truncover ;
  input Brand $ Amount Ratio ;
datalines;
Brand A,20,.4
Brand B,16,.5
Brand C,12,.25
Brand D,8,.25
Brand E,14,0
;
proc sql;
  create table want as 
  select a.brand,a.amount,a.ratio
       , b.brand as brand2,b.amount*a.ratio as new_amount
  from start a
     , start b
  ;
quit;
proc report data=want ;
  columns brand amount ratio new_amount,brand2;
  define brand / group;
  define amount / group;
  define ratio / group;
  define brand2 / across ' ';
  define new_amount / sum;
run;