CODEWITHSUNDEEP
sassas-macro
Eder Domingues
Eder Domingues

Reputation: 57

Using the result of a query as a parameter for another query

I am trying to use a query result as a parameter for another query.

As below:

PROC SQL;
    SELECT mydate INTO : varmydate FROM work.MyTable WHERE codigo = 1234;
QUIT;
 
PROC SQL;
    SELECT * FROM work.MyOtherTable
    WHERE codata = &varmydate;
QUIT;

But, unfortunately, this didn't work.

In this example, the varmydate variable will receive a value of type data in the first query.

 96         PROC SQL;
 97             SELECT codata FORMAT date9., valor FROM work.MyOtherTable WHERE codata = &varmydate;
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 NOTE: Line generated by the macro variable "VARMYDATE".
 97          01MAR2020
               _______
               22
               76
 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET, 
               GROUP, GT, GTT, HAVING, LE, LET, LT, LTT, NE, NET, OR, ORDER, ^=, |, ||, ~=.  
  ERROR 76-322: Syntax error, statement will be ignored.
  98         QUIT;

After executing the first query, the command below:

%PUT &varmydate;

will have the following result:

01MAR2020

But the second query returns an error.

If I use the syntax below:

PROC SQL;
    SELECT * FROM work.MyOtherTable
    WHERE codata = '01MAR2020'd;
QUIT;

then it will work.

Upvotes: 1

Views: 649

Answers (1)

Richard
Richard

Reputation: 27508

You did not indicate what the LOG window messaged regarding didn't work

I presume column mydate in table mytable is type character storing the date9. represention of a date. If it had instead been numeric variable with a date format the macro variable myvardate would have a value such as 21975

Thus, you need to resolve the date9 representation in a SAS date literal context

    WHERE codata = "&myvardate"d;

Double quotes are needed because macro resolution will not occur within single quoted literals.

Upvotes: 3

Related Questions