SAS

Macro Quoting in SAS Data Integration Studio

by  •  • Comments Off on Macro Quoting in SAS Data Integration Studio

This post was kindly contributed by From a Logical Point of View » SAS - go there to comment and to read the full post.

You can run the following piece of codes successfully in these 3 SAS programming environments:

  • BASE SAS
  • Enterprise Guide: create a new “File-New-Program”
  • SAS Data Integration Studio: create a new “Tool-Code Editor”

%let species="Setosa" "Versicolor";

data a;
    set sashelp.iris;
    where species in (&species);
run;

Then create a Transformation in SAS Data Integration Studio (DIS for short; I use version 4.3 in a Win 7 machine) using the codes above as source code (remember deleting first line) and create a simple Prompt to assign the macro variable &species with default values as "Setosa" "Versicolor":

SAS_DIS_Transformation

Drag this Transformation in a single node job and run, then you will get such errors:

127        data a;
128            set sashelp.iris;
129            where species in (&species);
NOTE: Line generated by the macro variable "SPECIES".
129        "Setosa", "Versicolor"
           _
           22
            _
            76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
              a missing value, -. 

ERROR 76-322: Syntax error, statement will be ignored.

130        run;

So, what happened inside DIS since such codes go well in BASE SAS, SAS EG and the Code Editor in DIS itself? The default value "Setosa" "Versicolor" was assigned to macro variable &species in the DIS Prompt (see picture above) and you would expect the following effect like what I wrote in open codes in BASE SAS:

%let species="Setosa" "Versicolor";

Actually NO. In DIS, this action was translated into such clause:

%let species = %nrquote(%"Setosa%", %"Versicolor%");

Here is the point: since there quotation symbols were masked by %nrquote function, they are no longer valid syntax characters (then we got errors!).

The solution: use a %unquote function on the macro variable &species to reverse the masking effort by %nrquote(note in our open codes example in BASE SAS, both functions were not used):

data a;
    set sashelp.iris;
    where species in (%unquote(&species));
run;

Notes on Macro Variable Generated by DIS

%let species = %nrquote(%"Setosa%", %"Versicolor%");

1)%nrquote function used while it is dated for a long time… %nrquote and %quote were replaced by %BQUOTE and %NRBQUOTE Functions.

2)All quotation symbols(“) were preceded by a percent sign (%). That’s %nrquote and %quote needed and why they are outdated any more.

This post was kindly contributed by From a Logical Point of View » SAS - go there to comment and to read the full post.