topic Re: how to insert a macro variable value as a statement in data step in SAS Programming

how to insert a macro variable value as a statement in data step

ksfang — Tue, 04 Apr 2017 15:10:05 GMT

I have a macro variable (&stmt1) whose value is

varname = 'varvalue';

How can I put this macro variable's value in a data step to add this varname into the dataset?

what I want to do is this:

data dataset1;

set dataset1;

varname='varvalue'; /* this is done by insert the macro variable &stmt1 */

run;

Re: how to insert a macro variable value as a statement in data step

Jagadishkatam — Tue, 04 Apr 2017 15:30:23 GMT

Please use the symget wth macro variable name. consider that the macro variable name is varvalue then i write

varname=symget('varvalue');

Re: how to insert a macro variable value as a statement in data step

ballardw — Tue, 04 Apr 2017 15:36:45 GMT

It might help to show your actual macro variable creation statement so we can tell which is your macro variable and which is the value of the variable. Too much "var" stuff hanging around.

Is this what you are looking for?

%let macrovar= variablename='abc';

data junk;
  &macrovar;
run;

I recommend that you do not use the
Data dataset1;

set dataset1;

until you have enough experience that you don't need to ask this simple of question. It is entirely too easy to destroy your data or cause problems that are difficult to diagnose with that code structure.

Re: how to insert a macro variable value as a statement in data step

RW9 — Tue, 04 Apr 2017 15:49:09 GMT

I would really advise against such a setup. Why have the variable in the macro variable, that doesn't make sense, the point of macro is flexibility, your removing that. Do:

%let stmt1=varvalue;

data dataset1;
  set dataset1;
  varname="&stmt1.";
run;

Far clearner and easier to maintain.

Re: how to insert a macro variable value as a statement in data step

ksfang — Tue, 04 Apr 2017 16:02:28 GMT

Hi RW9,

Thank you for your reply.

The issue is that I am reading in a spreadsheet, for the varname and varvalue. E.g.

Column A Column B

var1 eq 'abc'

var2 eq 'xyz'

var3 eq '123'

So in my macro I do not know the varname specifically, which needs to be read in from a spreadsheet.

Thanks,

Re: how to insert a macro variable value as a statement in data step

PaigeMiller — Tue, 04 Apr 2017 16:25:59 GMT

@ksfang wrote:

Hi RW9,

Thank you for your reply.

The issue is that I am reading in a spreadsheet, for the varname and varvalue. E.g.

Column A Column B

var1 eq 'abc'

var2 eq 'xyz'

var3 eq '123'

So in my macro I do not know the varname specifically, which needs to be read in from a spreadsheet.

Thanks,

This certainly isn't the same problem that was mentioned at the start of this thread. There, you were using an equal sign and the statement you were trying to create was assigning values. Now, you have the operator eq, which can only be used in WHERE statement or an IF statement, you are not assigning values. So this is a very confusing explanation.

Given that I'm not sure what you are actually trying to do, I nevertheless think that perhaps the solution is something like this (and if there are further clarifications on the actual problem, I reserve the right to retract my solution). You should read the column A values into it's own macro variable (for example &colA) and read the column B values into its own macro variable (for example, &colB) and then in the proper place in the data step, you use

&colA &colB

Re: how to insert a macro variable value as a statement in data step

ksfang — Tue, 04 Apr 2017 16:27:02 GMT

Thank you! Let me try...

Re: how to insert a macro variable value as a statement in data step

Tom — Tue, 04 Apr 2017 16:31:29 GMT

If you want to use the value of a macro variable as a statement in a data step then just do that. Macro variable resolution is just simple text substitution.

%let mvar=x2 = x**2 ;
data want;
  input x;
  &mvar ;
cards;
1
2
3
;

But if you want to generate a lot of code from a data set then I would avoid macro variables and just write the code to file. You can then use %INCLUDE to place it where you want it. This has the advantages of avoiding macro variables and worries of macro quoting and also you can look at the file after it is generated and debug the code generating step more easily.

filename code temp;
data _null_;
   set my_metadata ;
   put target '=' expression ';' ;
run;

data want ;
  set have ;
%include code /source2 ;
run;

Re: how to insert a macro variable value as a statement in data step

RW9 — Tue, 04 Apr 2017 17:36:52 GMT

Ah, ok, I see what you are doing. I would really advise you don't go down that route. I have seen many attempts to put programming in spreadsheets, and as Excel isn't good for data it really isn't good for data. You will need to do all kinds of validation on it, otherwise you code will fall over each run. Now, even if you still go with that method, even then I would advise to change the structure of the Excel file separating the items out, this will make your validation easier (and you can setup data validation in the Excel file also to reduce entry options), e.g:

ColumnA Operator ColumnB

var1 eq abc

A second point, macro variable are not a good place for coding elements either. Simpler to use the read in data to generate the code:

data _null_;
  set imported_data end=last;
  if _n_= then call execute('data want; set have;');
  call execute(cat('if ',columna,' ',operator,' "',columnb,'" then flag="y";'));
  if last then call execute('run;');
run;

This will generate a datastep with one if for each row in imported data, just and example.

Re: how to insert a macro variable value as a statement in data step

RW9 — Tue, 04 Apr 2017 17:36:53 GMT

ColumnA Operator ColumnB

var1 eq abc

A second point, macro variable are not a good place for coding elements either. Simpler to use the read in data to generate the code:

data _null_;
  set imported_data end=last;
  if _n_= then call execute('data want; set have;');
  call execute(cat('if ',columna,' ',operator,' "',columnb,'" then flag="y";'));
  if last then call execute('run;');
run;

This will generate a datastep with one if for each row in imported data, just and example.

Re: how to insert a macro variable value as a statement in data step

ksfang — Tue, 04 Apr 2017 18:43:42 GMT

Thank you all for your suggestion.