Declaration of date-variables at the begin of the script

[karlomutschler]

hello and good day

in my SAS script I have the following proc sql statement:

rsubmit;
proc sql;
create table myTable as
(
select ProductTarif,
sum(number_of_items)
from Orders
where Sales_Channel in ('A', 'B', 'C')
and ProductTarif in ('X', 'Y', 'Z')
and EFFECTIVE_REPORT_DATE >= '26DEC2005'd
group by ProductTarif
);
quit;
endrsubmit;

With variations of the EFFECTIVE_REPORT_DATE this proc sql statement
is repeated a number of times thoughout the script.

Therefore I would like to declare a number of variables (like
'Period', 'CurrMon', 'CurrWeek' ) once at the start of the script
to take care of the various instances of EFFECTIVE_REPORT_DATE
throughout the script.

How do I .....?

Any advice in this respect would be highly appreciated.

Kindest regards
Karlo

 

[ChrisW75]

How about something like this:-

%let curr_wk = '20FEB2006'd;
%let period = '01JUN2005'd;


%macro mk_mytab(rep_dt);
%syslput report_dt = &rep_dt;
rsubmit;
proc sql;
    create table myTable as
        (
        select    ProductTarif,
                sum(number_of_items)
        from        Orders
        where        Sales_Channel in ('A', 'B', 'C')
        and        ProductTarif in ('X', 'Y', 'Z')
        and        EFFECTIVE_REPORT_DATE >= &report_dt
        group by    ProductTarif
        );
    quit;
endrsubmit;
%mend;

%mk_mytab(&curr_wk);
%mk_mytab(&period);

This can be simplified if your entire program is remote submitted, as you won't need the syslput (which sets a macro variable in your remote session).
:-

rsubmit;

%let curr_wk = '20FEB2006'd;
%let period  = '01JUN2005'd;

%macro mk_mytab(rep_dt);
proc sql;
    create table myTable as
        (
        select    ProductTarif,
                sum(number_of_items)
        from        Orders
        where        Sales_Channel in ('A', 'B', 'C')
        and        ProductTarif in ('X', 'Y', 'Z')
        and        EFFECTIVE_REPORT_DATE >= &rep_dt
        group by    ProductTarif
        );
    quit;
%mend;

%mk_mytab(&curr_wk);
%mk_mytab(&period);

endrsubmit;