Search code examples
sqlsasenterprise-guide

How to call a prompt that selects a date range when I create a table using proc sql?

I would like to ask how to call a prompt (I am using SAS Enterprise Guide) in a proc sql that creates a table. I am interested in associate a number to date month (as in the table below) where d_month can be selected by using the prompt. 

time_window       d_month     database_name   table_name    short_name    group_field               field                  field_list
        1         '2018-02-28'       Dtb1         table1       Tab1        date_month department      Tab1.id Tab1.balance   id balance
        1         '2018-02-28'       Dtb1         table2       Tab2        date_month                  Tab2.credit            credit
        ...
        2         '2018-03-31'       Dtb1         table3       Tab3        date_month department       Tab3.debit             debit
        2         '2018-03-31'       Dtb2         table4       Tab4        date_month department    ...

I used 

   proc sql noprint;
    select time_w into time_w_lower from &time_table..time_table where time_last="&time_win_min_end"d;
    quit;

    proc sql noprint;
    select time_w into time_w_upper from &time_table..time_table where time_last="&time_win_max_end"d;
    quit;

to create the minimum and maximum date (and this should be the same of the prompt), but I do not know how to use this information when I select the fields for a new table. Time and date month (d_month) are in one table (&time_table..time_table), whereas the other fields are in another one (&dtb..&input.). What I tried is: 

   proc sql;
    create table table_test4 as
    select t.time_window
           ,t.d_month
           ,&alias..date_month
           ,&alias..department
           ,%qsysfunc(dequote(&_factor.))
    from &dtb..&input. &alias.
    cross join time_window t
    order by 1,2,3,4;
    quit;

where time_window is the table created as follows: 

  data time_window (drop=i);
      attr time_window length=8 format=11. informat=11.;
      attr d_month length=14 format=$12. informat=$12.;
      do =&time_w_lower. to time_w_upper.;
        time_window=left(put(i,4.));
        d_month=CAT("'", put(intent('month',"01JAN2010"d, i-1, 'end'), YYMMDDD10.),"'");
        output;
      end;
    run;

Unfortunately I have no data that I can show, so any example you will provide me would be greatly appreciated.

Thank you for your time.

Solution

    1. You should create promt with data range type: enter image description here

    2. Add created prompt to your node. enter image description here

    3. Then you can use generated macro variables from this prompt. To know what variable was generated you can run:

       %put _ALL_;

    GLOBAL DATE_MAX 31Dec2019

    GLOBAL DATE_MAX_LABEL December 31, 2019

    GLOBAL DATE_MIN 01Dec2019

    GLOBAL DATE_MIN_LABEL December 01, 2019

    So, in proc sql you can use "&DATE_MAX."d and "&DATE_MIN."d whete DATE is your prompt name.