Creating and calling macro variables from a dataset

[Babloo]

In my project we've decided to replace the hard coded values with the macro variables. Plan is to create the dataset with the list of hard corded values as one variable and the meaningful name for those values in another variable.

E.g.

Variables Values

Sex 'M'
Key_figure '22365'
Id '659'

And we would like to substitute the hard coded values in our program with &sex, &key_figure....

Appreciate if some one of you guide me by explaining how this approach works and how it can be implemented.

[Shmuel]

I assume that what you have is something like:

data macro_vars;
   infile datalines;
   input varname $ value $;
datalines;
sex  m
key_figure  22365
id  659
;
run;

and you are looking for the next step:

data _null_;
 set have;
  call symput(varname, strip(value));
run;

%put &sex &key_figure &id;

[Babloo]

How will you handle the situation to tweak the program if the example
records which I gave is in different program?

[Kurt_Bremser]

---

@Babloo wrote:
How will you handle the situation to tweak the program if the example
records which I gave is in different program?

---

As long as the dataset is in a permanent library, you just read it in the program where you need the macro variables.

[Shmuel]

---

@Babloo wrote:
How will you handle the situation to tweak the program if the example
records which I gave is in different program?

---

Easy to be done, depending on the format of your macro variables: sas dataset or external file.

Do you need to change your current code with hard coded variables into a new code

using the macro variables ? That also is programmable, depending on how long is your code.

If it short - better do manually.

[Babloo]

Answer to your questions:

a. SAS dataset
b. Yes, have to replace the hard coded values with macro variables in a
programs

[Kurt_Bremser]

Use call symputx()  to create macro variables in a data step.

[Cynthia_sas · Accepted solution]

Hi:

  Building on @Shmuel and @Kurt_Bremser posts, here's an example that uses slightly changed variable names and then uses the macro variables in a WHERE statement -- just to show usage:

data macro_vars;
   length varname $10 value $15;
   infile datalines;
   input varname $ value $;
   call symputx(varname, left(value),'G');
datalines;
sex  F
key_figure  Alice
height  65.9
;
run;

%put &=sex &=key_figure &=65.9;
 
proc print data=sashelp.class;
  where (sex="&sex" and name="&key_figure") 
         or
	height gt &height;
run;

Produces this output:

Hope this helps,

Cynthia

[Babloo]

Now if I have to call the values from SQL table instead of SAS dataset, do
I need to change any thing in your proposed code?

I will create a library for the SQL table.

[Kurt_Bremser]

---

@Babloo wrote:
Now if I have to call the values from SQL table instead of SAS dataset, do
I need to change any thing in your proposed code?

I will create a library for the SQL table.

---

A dataset is a dataset, no matter how you use it (data step, procedure step, proc SQL) or where it resides (native SAS dataset file, remote DBMS, inside a library defined with Excel engine, ...).

You use the same data _null_ step with call symputx().

[Babloo]

So if the program has ' where sex='M' ' then I can write it as ' where sex=&macrovariable '?

[Shmuel]

---

@Babloo wrote:
So if the program has ' where sex='M' ' then I can write it as ' where sex=&macrovariable '?

---

That's right.

[Tom]

---

@Babloo wrote:
So if the program has ' where sex='M' ' then I can write it as ' where sex=&macrovariable '?

---

Yes, but.

Since SEX is a character variable in the SAS code you had to add quotes around the letter M so that the compiler knew you meant the string with the value M and not a variable named M.  So if you use

sex=&macrovariable

Then value of macrovariable needs be

"M"

and not just

M

But having those quotes in the value of macro variable will then make it hard to also use that M as part of a word in the generated SAS code. For example as part of a dataset name.  With the quotes this wouldn't make sense.

data out&macrovariable ;
  set sashelp.class;
  where sex=&macrovariable;
run;

You might consider adding the quotes into the code where the code needs them instead of adding them to the value of the macro variable.

data out&macrovariable ;
  set sashelp.class;
  where sex="&macrovariable";
run;