CODEWITHSUNDEEP
sqlsas
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

Making Dynamic SQL Queries in SAS Proc Sql

Here's an edit that reflects some progress made:

I have the following function that works:

proc fcmp outlib=mydir;
 function sqlWhere(interval $, myDate $) $;
 ...
 return("id");
 endsub;
quit;

This is tested and works fine. So I tried:

%macro sqlWhere(interval, myDate);
  &interval.("year") AS t
  &myDate.("someDateField") AS tt
%mend;
proc sql;
  CREATE TABLE test AS (
    SELECT %sqlWhere(t, tt)
    FROM myTable);
quit;

The top part runs fine when I run selection. However, the proc sql blows up and says I'm missing a comma. I'm confused because the function is returning " id " in other tests which should make the code work. The error says there is a missing comma on the "tt" part...

I'm attempting to make a dynamic query in SAS. I'm having a couple of issues and I'm not sure if what I want to do is possible. Also, sorry for deleting a prior question; I wanted to give a better explanation.

Say I have this code:

proc sql;
SELECT 
  YEAR(myDate) AS yr, 
  MONTH(myDate) AS mo,
  id
FROM
  myTable;
run;

I'm trying to make it conditional. This gives two problems. First, I can't get the basic syntax to work. Second, I can't get my custom function to create the proper string.

I want something like this:

%let a = sqlDate("month");
proc sql;
SELECT
  &a
FROM
  myTable;
run;

This structure doesn't work, even when I forgo the function and just enter 

%let a = "YEAR(myDate) AS yr, MONTH(myMonth) AS mo, id";

Is something like this possible?

My second issue is how to construct the function itself, but I want to confirm I can even do something like this first. I'm basically putting an indicator in a master program that is either "day", "week", "month" or "year" and then telling the program to query SQL in a given way. Can I pass whole strings someway? Is it possible to build strings based on inputs in this manner?

Upvotes: 2

Views: 4596

Answers (1)

mvherweg
mvherweg

Reputation: 1283

Your first issue is explained by a misunderstanding of SAS macro. When you put this:

%let a = "YEAR(myDate) AS yr, MONTH(myMonth) AS mo, id";

SAS will put into your query:

"YEAR(myDate) AS yr, MONTH(myMonth) AS mo, id"

I.e.: it will keep the quotes. If you want to 'hide' things like functions away when putting it in a SAS macro variable, you have to use the %str() function instead of " or '. So if you would write:

%str(YEAR(myDate) AS yr, MONTH(myMonth) AS mo, id);

You would have something that works.

To answer your second question: that is certainly possible. But keep something very important in mind: any macro function, macro variable defintion or call to a macro variable that is in open code, is interpreted and replaced before your SAS code runs. If you have a macro variable/function that depends on results from a SAS data step or proc, you need to encapsulate it in a macro function: everything in it is only interpreted and replaced during runtime.

EDIT To answer your comment: it is mainly general advice i'm passing along because you seem unfamiliar with SAS Macro. In some cases, you can get weird results where it looks like your macro variable was not set/changed and if you're not aware of it, it can be frustrating. (and we all ran into it for a first time ;))

In your specific case, if we assume you have made an sqldate macro that returns the appropriate select part, you're probably best off putting it immediately into the query. e.g.:

%macro sqldate(period);
    &period.(mydate) as period
%mend;
/*note no semicolon, since a macro function is basically the same as saying: resolve this piece of macro code and add the result directly to my written code.*/

proc sql;
    select %sqldate(month)
    from mytable;
quit;
/*also note: you end proc sql with a quit statement instead of run*/

Upvotes: 5

Related Questions