topic Re: easy macro: looping on a data set to select and aggregate data in another data set in SAS Programming

easy macro: looping on a data set to select and aggregate data in another data set

Sir_Highbury — Tue, 02 Aug 2016 09:36:30 GMT

Dear experts,

here my input data:

1. a data set where for each id I specify the time period with begin and end (YYYMM)

data T00_00_PTF;
input reference_period: $12. begin: comma5. end: comma5.;
datalines;
current_month 201606 201606
previous_month 201605 201605
previous_12m 201507 201606
previous_36m 201307 201606
;

data T00_00_PTF;
set T00_00_PTF;
id=cats(reference_period,"_",begin,"_",end)
;run;

2. a data set of row data where the field date define the YYYYMM:

data have;
input date: comma5. revenue: comma5. shop: $12. country: $2.;
datalines;
201606 10000 ABC UK
201606 20000 A JP
201605 30000 A JP
201508 40000 A JP
201605 30000 BC UK
201308 20000 BC UK
;

on the base of the table T00_00_ptf I would like create another data set have which is expanding the initial data set have, adding to each observation the field id from the data set T00_00_ptf in the case that the variable data is in the time range. Here the output that I expect for the first observation (date=201606 => included in all the subset defined in the table T00_00_PTF)

data want;
input id: $25. date: comma5. revenue: comma5. shop: $12. country: $2.;
datalines;
current_mont_201606_201606 201606 10000 ABC UK
previous_mon_201605_201605 201606 10000 ABC UK
previous_12m_201507_201606 201606 10000 ABC UK
previous_36m_201307_201606 201606 10000 ABC UK
;

Any suggestion? I tried using a proc sql with the left outer join (left outer join T00_00_ptf t5. on (t5.begin=<t0.date and t0.date<=t5.end) but actually I would like to run it with a macro, so I can use it in the future also for different purposes.

Thanks a lot, SH.

Re: easy macro: looping on a data set to select and aggregate data in another data set

RW9 — Tue, 02 Aug 2016 10:11:21 GMT

If you already have working code, then you just need to parameterise that code to make a macro:

proc sql;
  create table WANT as 
  select * from SASHELP.CARS;
quit;

Could become:

%macro Create_Table (ds=,frm=);
  proc sql;
    create table &DS. as
    select * from &FRM.;
  quit;
%mend Create_Table;

%Create_Table (ds=WANT,frm=SASHELP.CARS);

However, in my experience, macro code merely complicates processes and in 99% of cases is not worth it in the first place, as SAS Base has inbuilt functionality to do things.

Re: easy macro: looping on a data set to select and aggregate data in another data set

gamotte — Tue, 02 Aug 2016 10:12:35 GMT

The solution you describe seems OK to me. Since you do not indicate which other purposes you want to

use your program for, it is difficult to give you an answer.

Also, i think previous_month should not be included in your want dataset since begin and end are 201605.