SAS - Dynamically creating macro variable via concatenation of text to a current macro variable

I'm looking to create a macro variable. The macro variable name needs to be made by concatenating text to an already existing variable after it has resolved. Specifically, I want to do this in a PROC SQL block using INTO:. Here's a snippet to explain what I want to do.

%macro MyMacro(process);
    PROC SQL;
        SELECT
           COUNT(*) INTO: &process._new_text
        FROM
            DataSetHere
    ;QUIT;
%mend MyMacro;

If I call on this macro and I pass the word "cat" into process, I want to have now declared/initialized a variable with the name cat_new_text, and it should return the COUNT(*) selected in that query whenever &cat_new_text is referenced.

I've done a little reading around, looked into using multiple ampersands, trying to resolved &process within quotes first - nothing has really solved my exact problem. Does anyone know of a clear cut way to accomplish this?

Thanks in advance!

Solution

Your code seems fine and it seems like it would do exactly what you describe. However if your trying to "access" that new macro variable outside the macro, e.g.,

%MyMacro(cat);
%put &cat._new_text.;

then indeed it will not work because the variable was created locally in your macro and does not exist outside the scope of that macro.

To fix that, you simply need to add a %global statement in your macro definition:

%macro MyMacro(process);
  %global &process._new_text;
  PROC SQL;
    SELECT COUNT(*)
    INTO: &process._new_text
    FROM DataSetHere
    ;
  QUIT;
%mend MyMacro;