SAS: Creating Tables Dynamically for different dates and table names in one go

Question

I'm stuck to something about creating tables dynamically by date filter.

I have SAS code for setting date filters before running main codes.

This it the date code;

data _null_;

/*ACTUAL DATES*/
R_act_beg=intnx('month',today(),-12,'beginning');
call symput('R_act_beg',R_act_beg);
R_act_end=intnx('month',today(),-1,'end');
call symput('R_act_end',R_act_end);
name_m=put(month(intnx('month',today(),-1)),z2.);
call symput('name_m',name_m);
name_y_act=put(year(intnx('month',today(),-1)),z4.);
call symput('name_y_act',name_y_act);
nameR_act=name_m||substr(name_y_act,3,4);
call symput('nameR_act',nameR_act);

And this is the one of main codes;

PROC SQL;
CREATE TABLE DATA.PREMIUM&nameR_act  AS 
SELECT t1.POLICY_NO, 
      /* SUM_of_PREMIUM */
        (SUM(t1.PREMIUM)) FORMAT=22.2 AS SUM_of_PREMIUM
  FROM WH.V_PRODUCT t1
  WHERE t1.ISSUE_DATE BETWEEN &R_act_beg AND &R_act_end AND t1.PRODUCT_NO IN 
       (
       '421',
       '423',
       '424',
       '425',
       '404',
       '433',
       '430',
       '444',
       '441',
       '443',
       '453'
       )
  GROUP BY t1.POLICY_NO;
QUIT;

So I should run about ten main codes retrospectively for 12 months. Is it possible to create a date code dynamically in order to running main codes for 12 months in one go.

why don't you enclose the main code in a macro and pass on the dates parameters one by one - 12 times? — in_user, May 22 '15 at 08:11

score 3 · Accepted Answer · answered May 22 '15 at 08:50

You could build on the following code

Step1 : Creating the dataset which contains all the 12 dates.

Not sure how are you calculating the dates for all the 12 months, So I have assumed dataset All_dates contains all your dates with variables - R_act_beg, R_act_end ,name_m,name_y_act,nameR_act. This dataset contains 12 records one for each month.

Step2: Creating macro variables for all the 12 months in one go

proc sql;
select R_act_beg into: R_act_beg1 - :R_act_beg12 from all_dates;
select R_act_end into: R_act_end1 - :R_act_end12 from all_dates;
select name_m into: name_m1 - :name_m12 from all_dates;
select name_y_act into: name_y_act1 - :name_y_act12 from all_dates;
select nameR_act into: nameR_act1 - :nameR_act12 from all_dates;
quit;

Step3: Running the code 12 times by using the do loop and &&

%macro run_12_times;

%do i=1 %to 12; 
PROC SQL;
CREATE TABLE DATA.PREMIUM&nameR_act&&i.  AS 
SELECT t1.POLICY_NO, 
      /* SUM_of_PREMIUM */
        (SUM(t1.PREMIUM)) FORMAT=22.2 AS SUM_of_PREMIUM
  FROM WH.V_PRODUCT t1
  WHERE t1.ISSUE_DATE BETWEEN &R_act_beg&&i. AND &R_act_end&&i. AND t1.PRODUCT_NO IN 
       (
       '421',
       '423',
       '424',
       '425',
       '404',
       '433',
       '430',
       '444',
       '441',
       '443',
       '453'
       )
  GROUP BY t1.POLICY_NO;

  %end;

  %mend;
%run_12_times;

Thanks @NEOmen, i've a problem with my sas account. when i fix it i'm gonna try it and feedback to you. Thans again. — Doganay Yildiz, May 25 '15 at 07:40
Hi @NEOmen, there is no problem for step 1 and 2 but at step 3 sas code is getting crashed. a part of error log; '..5 %run_16_times; WARNING: Apparent symbolic reference NAMER_ACT not resolved. NOTE: Line generated by the invoked macro "RUN_16_TIMES". 75 &nameR_act&&i. AS SELECT t1.POLICE_NO, 22 200 75 ! FORMAT=22.2 AS SUM_of_PREMIUM FROM WH.V_PRODUCT t1 WHERE t1.ISSUE_DATE BETWEEN &R_act_beg&&i. AND...' — Doganay Yildiz, Jun 11 '15 at 10:00

score 2 · Answer 2 · answered Jun 12 '15 at 05:50

I solved the problem whereby @NEOmen 's answer but i want to fix a slip up at "Step 3". "Step 1" and "Step 2" completely correct. "Step 3" should be as follows. Thanks again.

%macro run_12_times;

%do i=1 %to 12; 
PROC SQL;
CREATE TABLE DATA.PREMIUM&&nameR_act&i.  AS 
SELECT t1.POLICY_NO, 
      /* SUM_of_PREMIUM */
        (SUM(t1.PREMIUM)) FORMAT=22.2 AS SUM_of_PREMIUM
  FROM WH.V_PRODUCT t1
  WHERE t1.ISSUE_DATE BETWEEN "&&R_act_beg&i."d AND "&&R_act_end&i."d AND t1.PRODUCT_NO IN 
       (
       '421',
       '423',
       '424',
       '425',
       '404',
       '433',
       '430',
       '444',
       '441',
       '443',
       '453'
       )
  GROUP BY t1.POLICY_NO;

  %end;

  %mend;
%run_12_times;

SAS: Creating Tables Dynamically for different dates and table names in one go

2 Answers2