CODEWITHSUNDEEP
sassas-macro
Gus
Gus

Reputation: 11

Macro Function assign output to Macro variable

I am trying to pass the value of this macro to output macro variable

%Macro Get_expression(tablename,code);
    proc sql;
        select Sql_expression
        From &tablename.
        where Cod = &code.;
    quit;
run;
%MEND Get_data;

*Print the macro

%let output=%Get_expression(dtvault.parameterjobs,'DO_M_R_C');
%put &output;

but I'm having this error

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         %Macro Get_expression(tablename,code);
27             proc sql;
28              select Sql_expression
29              From &tablename.
30              where Cod = &code.;
31          quit;
32         run;
33         %MEND Get_data;
WARNING: Extraneous text on %MEND statement ignored for macro definition GET_EXPRESSION.
34         
35         *Calling a Macro program;
36         %let output=%Get_expression(dtvault.parameterjobs,'DO_M_R_C');
NOTE: Line generated by the invoked macro "GET_EXPRESSION".
36         proc sql;   select Sql_expression   From &tablename.   where Cod = &code.;  quit; run;
                       ______
                       180
ERROR 180-322: Statement is not valid or it is used out of proper order.


37         %put &output;

What am I doing wrong?

If a run only this 

%Get_expression(dtvault.parameterjobs,'DO_M_R_C');

the tab result apears with query result correct

Thanks, G.O

Upvotes: 1

Views: 1337

Answers (3)

Tom
Tom

Reputation: 51566

The macro just generates text. In your case the beginning of the text that it generates is 

proc sql; select Sql_expression

So when you use it inside of another statement, like your %LET statement

%let output=%Get_expression(dtvault.parameterjobs,'DO_M_R_C');

the macro call gets replaced by the generated text. So you end up with code like this that SAS will now need to evaluate.

%let output=proc sql; select Sql_expression

The first semi-colon marks the end of the %LET statement. So the macro variable OUTPUT has the value proc sql. And the SELECT statement is invalid because you never actually started PROC SQL.

Upvotes: 0

Reeza
Reeza

Reputation: 21264

You have a couple of issues.

  1. The name at the %MEND needs to match the macro name (get_expression vs get_data)

  2. You don't actually create any output or macro variable. The output goes to the ODS window. You can modify your code by doing a select into : to create macro variable but you need to make sure you know the name and assign the correct scope (local/global). But you're trying to use a macro as a function - which it's not. PROC FCMP is designed to build functions, this looks a lot like a lookup table instead.

This is how I would do this, where it's looking up the weight of a person in the SASHELP.CLASS data set.

%macro get_data(tablename=, name=, output_name=);
     data _null_;
          set &tablename.;
          where name= &name. ;
          call symputx("&output_name.", weight, 'g');
     run;
%mend;

%get_data(tablename=sashelp.class, name='Alfred', output_name=myVar);
%put &myVar;

Upvotes: 0

Petr
Petr

Reputation: 376

If I understand your question correctly, you are looking for select into statement. Here is an example:

proc sql noprint;
    select "'" || strip(name) || "'" /*format=*/ into :name_list separated by ", "
    from sashelp.class;
quit;
%put &name_list.;

Remember that SAS macro variables are always text. That's why formatting matters.

Upvotes: 1

Related Questions