topic Re: Creating and calling macro variables from a dataset in SAS Programming

Creating and calling macro variables from a dataset

Babloo — Sat, 21 Sep 2019 08:54:24 GMT

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.

Re: Creating and calling macro variables from a dataset

Shmuel — Sat, 21 Sep 2019 10:26:34 GMT

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;

Re: Creating and calling macro variables from a dataset

Kurt_Bremser — Sat, 21 Sep 2019 09:07:26 GMT

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

Re: Creating and calling macro variables from a dataset

Babloo — Sat, 21 Sep 2019 11:22:32 GMT

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

Re: Creating and calling macro variables from a dataset

Kurt_Bremser — Sat, 21 Sep 2019 11:51:17 GMT

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

Re: Creating and calling macro variables from a dataset

Babloo — Sat, 21 Sep 2019 13:06:12 GMT

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

Re: Creating and calling macro variables from a dataset

Shmuel — Sat, 21 Sep 2019 13:09:07 GMT

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

Re: Creating and calling macro variables from a dataset

Shmuel — Sat, 21 Sep 2019 13:10:09 GMT

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

That's right.

Re: Creating and calling macro variables from a dataset

Tom — Sat, 21 Sep 2019 14:05:09 GMT

@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

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;

Re: Creating and calling macro variables from a dataset

Babloo — Sat, 21 Sep 2019 13:32:32 GMT

Answer to your questions:

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

Re: Creating and calling macro variables from a dataset

Cynthia_sas — Sat, 21 Sep 2019 13:45:27 GMT

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

Re: Creating and calling macro variables from a dataset

Babloo — Sat, 21 Sep 2019 15:52:32 GMT

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.

Re: Creating and calling macro variables from a dataset

Kurt_Bremser — Sat, 21 Sep 2019 17:57:36 GMT

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