title: Making Dynamic SQL Queries in SAS Proc Sql
body: <p>Here's an edit that reflects some progress made:</p>
  
  <p>I have the following function that works:</p>
  
  <pre><code>proc fcmp outlib=mydir;
   function sqlWhere(interval $, myDate $) $;
   ...
   return("id");
   endsub;
  quit;
  </code></pre>
  
  <p>This is tested and works fine.  So I tried:</p>
  
  <pre><code>%macro sqlWhere(interval, myDate);
    &amp;interval.("year") AS t
    &amp;myDate.("someDateField") AS tt
  %mend;
  proc sql;
    CREATE TABLE test AS (
      SELECT %sqlWhere(t, tt)
      FROM myTable);
  quit;
  </code></pre>
  
  <p>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...</p>
  
  <p>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.</p>
  
  <p>Say I have this code:</p>
  
  <pre><code>proc sql;
  SELECT 
    YEAR(myDate) AS yr, 
    MONTH(myDate) AS mo,
    id
  FROM
    myTable;
  run;
  </code></pre>
  
  <p>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.</p>
  
  <p>I want something like this:</p>
  
  <pre><code>%let a = sqlDate("month");
  proc sql;
  SELECT
    &amp;a
  FROM
    myTable;
  run;
  </code></pre>
  
  <p>This structure doesn't work, even when I forgo the function and just enter </p>
  
  <pre><code>%let a = "YEAR(myDate) AS yr, MONTH(myMonth) AS mo, id";
  </code></pre>
  
  <p>Is something like this possible?</p>
  
  <p>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?</p>