topic Re: Using macro to create new column in New SAS User

Using macro to create new column

jlin4 — Sat, 02 Jan 2021 17:53:01 GMT

Hi, I am trying to make use of macro to create a column.

In the dataset below, I would like to create a new column "name3". Specifically, if class=1, then name3=name1, and if class=2, then name3=name2.

data test;
input name1$ name2$ class$;
cards;
adam billy a
charli dan b
eliot francis a
;
run;

I tried to do so with the following codes. However, name3 created is always equal to name2.

May I ask if there is a way in which I could modify my codes to get the desired results? Thank you.

%macro pickname(tb=, cl=, n1=, n2=);
	data &tb; set &tb;
		%if &cl="a" %then %do;
			name3 = &n1;
		%end;
		
		%else %if &cl="b" %then %do;
			name3 = &n2;
		%end;
	run;
%mend;

%pickname(tb=test, cl=class, n1=name1, n2=name2);

Re: Using macro to create new column

novinosrin — Sat, 02 Jan 2021 18:05:37 GMT

Hi @jlin4 Your %IF macro code would evaluate to False conditional check and so there wouldn't be any texts generated that can be sent to the compiler as a part of datastep execution.

The IF condition->

&cl="a" resolving to class="a" is false

and likewise class="b" is also false

Please review your logic

Re: Using macro to create new column

ballardw — Sat, 02 Jan 2021 18:13:16 GMT

@jlin4 wrote:

Hi, I am trying to make use of macro to create a column.

In the dataset below, I would like to create a new column "name3". Specifically, if class=1, then name3=name1, and if class=2, then name3=name2.
data test;
input name1$ name2$ class$;
cards;
adam billy a
charli dan b
eliot francis a
;
run;
I tried to do so with the following codes. However, name3 created is always equal to name2.

May I ask if there is a way in which I could modify my codes to get the desired results? Thank you.
%macro pickname(tb=, cl=, n1=, n2=);
	data &tb; set &tb;
		%if &cl="a" %then %do;
			name3 = &n1;
		%end;
		
		%else %if &cl="b" %then %do;
			name3 = &n2;
		%end;
	run;
%mend;

%pickname(tb=test, cl=class, n1=name1, n2=name2);

Please show your log. When I run your exact as posted code the variable name3 is not created at all.

With option Mprint set to run the first time:

201  %macro pickname(tb=, cl=, n1=, n2=);
202     data &tb; set &tb;
203        %if &cl="a" %then %do;
204           name3 = &n1;
205        %end;
206
207        %else %if &cl="b" %then %do;
208           name3 = &n2;
209        %end;
210     run;
211  %mend;
212
213  options mprint;
214
215  %pickname(tb=test, cl=class, n1=name1, n2=name2);
MPRINT(PICKNAME):   data test;
MPRINT(PICKNAME):   set test;
MPRINT(PICKNAME):   run;

NOTE: There were 3 observations read from the data set
      WORK.TEST.
NOTE: The data set WORK.TEST has 3 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

This non-assignment happens because this code:

%if &cl="a" %then %do;

and

%if &cl="b" %then %do;

are never true. The values you have provided for Cl is class, without quotes. So class is not equal to "a" or "b"

Macro %if cannot use the values the values of data step variables. So you may have wanted

%macro pickname(tb=, cl=, n1=, n2=);
	data &tb; set &tb;
		if &cl="a" then do;
			name3 = &n1;
		end;
		
		else if &cl="b" then do;
			name3 = &n2;
		end;
	run;
%mend;

Before attempting a macro have actual code that works without any macro variables or statements.

Then replace the bits, often one at a time, to make sure things work while testing.

One suspects that you THOUGHT Name3=Name 2 because at some earlier step you had different logic that assigned that value and did not return to using the actual original Test data set.

Second hint: The code structure of

Data name;

set name;

is very poor for testing recoding. It completely replaces the source data set unless there are errors that stop the step from running at all. So when you retest code the next time the not original variables now exist the data set and you cannot compare the start to the output data.

Third hint:

Learn Options MPRINT SYMBOLGEN LOGIC;

to create details of the code and program flow inside a macro.

Re: Using macro to create new column

Tom — Sat, 02 Jan 2021 18:18:40 GMT

Before designing the macro first create (or envision) the SAS code you want it to create.

Sounds like you want to create this code:

data test;
  set test;
  if class="a" then name3 = name1;
  else if class="b" then name3 = name2;
run;

Test it make sure it works (do you really want to destroy the original TEST dataset?)

Next figure out want parts you want to make dynamic

data test;
  set test;
  if class="a" then name3 = name1;
  else if class="b" then name3 = name2;
run;

and replace with macro variable references.

%let tb=test;
%let cl=class;
%let n1=name1;
%let n2=name2;
data &tb;
  set &tb;
  if &cl="a" then name3 = &n1;
  else if &cl="b" then name3 = &n2;
run;

Test it to make sure it works.

Now convert it to a macro

%macro pickname(tb,cl,n1,n2);
data &tb;
  set &tb;
  if &cl="a" then name3 = &n1;
  else if &cl="b" then name3 = &n2;
run;
%mend ;

And test it:

options mprint;
%pickname(tb=test, cl=class, n1=name1, n2=name2)

Re: Using macro to create new column

Shmuel — Sat, 02 Jan 2021 18:20:28 GMT

Macro program is just a generator of code.

Check line:

%if &cl="a"

suppose &cl resolve as a. then you compare a to "a" which is false.

If you meant to compare the value of a variable given by &cl then don't do it by a macro

but just by

If <variable name> = "a" ...

It seems that what you want to do is

data test;
 set test;
       if class = "a" then name3 = name1; else
       if class = "b" then name3 = name2;
run;

Suppose you want to replicate it for vaious tables with different variable names then you can do it with next code:

%macro pickname(tb=, cl=, n1=, n2=);
	data &tb; set &tb;
		if &cl = "a" then name3 = &n1; else
		if &cl = "b" then name3 = &n2;
	run;
%mend;

%pickname(tb=test, cl=class, n1=name1, n2=name2);

Re: Using macro to create new column

Quentin — Sat, 02 Jan 2021 18:25:22 GMT

When developing and debugging macro code, it's crucial to turn on the option MPRINT and review your log, so that you can see the SAS code that the macro generated. When I do that with your code, I get:

22   options mprint ;
23   %pickname(tb=test, cl=class, n1=name1, n2=name2);
MPRINT(PICKNAME):   data test;
MPRINT(PICKNAME):   set test;
MPRINT(PICKNAME):   run;

You can see that there is no assignment statement. Why not? Because both %IF statements evaluate to false.

It looks like you are trying to evaluate the value of a data step variable, and conditionally execute a data step assignment statement. To do that, you can use the data step IF statement, not the macro %IF statement.

If you do that, your code will look like:

%macro pickname(tb=, cl=, n1=, n2=);
	data &tb; set &tb;
		if &cl="a" then do;
			name3 = &n1;
		end;
		
		else if &cl="b" then do;
			name3 = &n2;
		end;
	run;
%mend;

And when you run it, the log will show that the macro generated a data step which looks like what you want:

23   %pickname(tb=test, cl=class, n1=name1, n2=name2);
MPRINT(PICKNAME):   data test;
MPRINT(PICKNAME):   set test;
MPRINT(PICKNAME):   if class="a" then do;
MPRINT(PICKNAME):   name3 = name1;
MPRINT(PICKNAME):   end;
MPRINT(PICKNAME):   else if class="b" then do;
MPRINT(PICKNAME):   name3 = name2;
MPRINT(PICKNAME):   end;
MPRINT(PICKNAME):   run;

As you work with the macro language, it's important to understand (and remember) the difference between %IF (a macro language statement for evaluating macro variable expressions) and IF (a data step statement for evaluating data step variable expressions), %DO vs DO, etc.

--Q.