CODEWITHSUNDEEP
sassas-macro
Kirstenz
Kirstenz

Reputation: 21

Use a macro instead of 25 proc sql steps?

I have a SAS code (SQL) that has to repeat for 25 times; for each month/year combination (see code below). How can I use a macro in this code?

proc sql;
create table hh_oud_AUG_17 as
select   hh_key
        ,sum(RG_count)                                  as RG_count_aug_17
        ,case when sum(RG_count) >=2 then 1 else 0 end  as loyabo_recht_aug_17
from basis_RG_oud
where valid_from_dt <= "01AUG2017"d <= valid_to_dt
group by hh_key
order by hh_key
;
quit;

proc sql;
create table hh_oud_SEP_17 as
select   hh_key
        ,sum(RG_count)                                  as RG_count_sep_17
        ,case when sum(RG_count) >=2 then 1 else 0 end  as loyabo_recht_sep_17
from basis_RG_oud
where valid_from_dt <= "01SEP2017"d <= valid_to_dt
group by hh_key
order by hh_key
;
quit;

Upvotes: 2

Views: 75

Answers (2)

Richard
Richard

Reputation: 27498

Create a second data set that enumerates (is a list of) the months to be examined. Cross Join the original data to that second data set. Create a single output table (or view) that contains the month as a categorical variable and aggregates based on that. You will be able to by-group process, classify or subset based on the month variable.

data months;
  do month = '01jan2017'd to '31dec2018'd;
    output;
    month = intnx ('month', month, 0, 'E');
  end; 
  format month monyy7.;
run;

proc sql;
  create table want as
  select 
    month, hh_key, 
    sum(RG_count) as RG_count,
    case when sum(RG_count) >=2 then 1 else 0 end  as loyabo_recht
  from 
    basis_RG_oud
  cross join
    months
  where 
    valid_from_dt <= month <= valid_to_dt
  group 
    by month, hh_key
  order 
    by month, hh_key
  ;

…
 /* Some analysis */
    BY MONTH;
…
 /* Some tabulation */
    CLASS MONTH;
    TABLE … MONTH … 
    WHERE year(month) = 2018;

Upvotes: 1

user667489
user667489

Reputation: 9569

If you use a data step to do this, you can put all the desired columns in the same output dataset rather than using a macro to create 25 separate datasets:

/*Generate lists of variable names*/
data _null_;
  stem1 = "RG_count_";
  stem2 = "loyabo_recht_";
  month = '01aug2017'd;
  length suffix $4 vlist1 vlist2 $1000;
  do i = 0 to 24;
    suffix = put(intnx('month', month, i, 's'), yymmn4.);
    vlist1 = catx(' ', vlist1, cats(stem1,suffix));
    vlist2 = catx(' ', vlist2, cats(stem2,suffix));
  end;
  call symput("vlist1",vlist1);
  call symput("vlist2",vlist2);
run;

%put vlist1 = &vlist1;
%put vlist2 = &vlist2;

/*Produce output table*/
data want;
  if 0 then set have;
  start_month = '01aug2017'd;
  array rg_count[2, 0:24] &vlist1 &vlist2;
  do _n_ = 1 by 1 until(last.hh_key);
    set basis_RG_oud;
    by hh_key;
    do i = 0 to hbound2(rg_count);
      if valid_from_dt <= intnx('month', start_month, i, 's') <= valid_to_dt 
        then rg_count[1,i] = sum(rg_count[1,i],1);
    end;
  end;
  do _n_ = 1 to _n_;
    set basis_RG_oud;
    do i = 0 to hbound2(rg_count);  
      rg_count[2,i] = rg_count[1,i] >= 2;
    end;    
  end;
run;

Upvotes: 1

Related Questions