Macro Loop for Merge Monthly to Quarterly Data

[Toni2]

i want to create a macro loop in which i will provide it with monthly datasets and it will merge them per quarter after applying some exclusions 

The below macro DOES NOT work i have added only to show how i believe the above can be coded 

First, macro excl applies some exclusions to all monthly_data and then in macro append i want to create 2 quarter datasets to include the monthly data from excl 

At the end i want to have the below two datasets 

- quarter_1 (this must include month_1 to month_3 from the excl macro)

-quarter_2  (this must include month_4 to month_6 from the excl macro)

However, the macro should be general in case that more months added for example 9 or 12 and so on

%let monhtly_data = month_1 month_2 month_3 month_4 month_5 month_6;
%let outcome_names = quarter_1 quarter_2;

%macro excl;
%let k=%sysfunc(countw(&monhtly_data));
%do i=1 %to &k;
%let monthly_var = %scan(&monhtly_data,&j);
 
data excl_data_&i;
set &monthly_var ;
run;

%end;
%mend;
%excl;


%macro append;
%let k=%sysfunc(countw(&monhtly_data));
%do i=1 %to &k;
%let raw_dt = %scan(&monhtly_data,&i);

proc append data=&outcome_names data=excl_data_&i force;
run;

%end;
%mend;
%append;

[Reeza]

Show how the code would look like for the example case and then it can be generalized.

[Toni2]

thanks, i have edited my initial comment and added some code

[PaigeMiller]

As always, you are STRONGLY urged to write code that works where you hard code the six months and two quarters and where it doesn't use any macros or macro variables. If you don't have code that works without macros and without macro variables, it will never work with macros or with macro variables.

Most people ignore this advice. Bad move.

Regarding your merge monthly to quarterly needs, there's nothing here that turns month1 through month3 into quarter 1. You need to work out that part of your code (without macros and without macro variables) as well. Once you get that working, it will be a whole lot easier to turn it into a macro.

[Toni2]

thanks 🙂

[Reeza]

Assuming your %excl macro works, then you could do something like the following. I guarantee this is not optimal though. 

%let monhtly_data = month_1 month_2 month_3 month_4 month_5 month_6;
%let outcome_names = quarter_1 quarter_2;

%macro excl;
%let k=%sysfunc(countw(&monhtly_data));
%do i=1 %to &k;
%let monthly_var = %scan(&monhtly_data,&j);
 
data excl_data_&i;
set &monthly_var ;
run;

%end;
%mend;
%excl;


%macro create_quarterly_files;

*determine number of quarters to be created;
%let nQuarters = %sysfunc(countw(&outcome_names));
*set counter for # of qtrs to 1;


%do i=1 %to &nQuarters;

*create macro variables for name of output datasets;
%let dsetout = %scan(&outcome_names, &i.);


%do j=1 %to 3;
proc append base=&dsetout. data=excl_data_%sysevalf((&i-1)*3 + &j);
run;



%end;

%end;

*would recommend this cleanup step;
/* proc datasets lib=work nolist nodetails; */
/* delete excl_data_:; */
/* run;quit; */


%mend;

options mprint symbolgen;
%create_quarterly_files;

options nomprint nosymbolgen;

---

@Toni2 wrote:

i want to create a macro loop in which i will provide it with monthly datasets and it will merge them per quarter after applying some exclusions 

The below macro DOES NOT work i have added only to show how i believe the above can be coded 

First, macro excl applies some exclusions to all monthly_data and then in macro append i want to create 2 quarter datasets to include the monthly data from excl 

At the end i want to have the below two datasets 

- quarter_1 (this must include month_1 to month_3 from the excl macro)

-quarter_2  (this must include month_4 to month_6 from the excl macro)

However, the macro should be general in case that more months added for example 9 or 12 and so on

%let monhtly_data = month_1 month_2 month_3 month_4 month_5 month_6;
%let outcome_names = quarter_1 quarter_2;

%macro excl;
%let k=%sysfunc(countw(&monhtly_data));
%do i=1 %to &k;
%let monthly_var = %scan(&monhtly_data,&j);
 
data excl_data_&i;
set &monthly_var ;
run;

%end;
%mend;
%excl;


%macro append;
%let k=%sysfunc(countw(&monhtly_data));
%do i=1 %to &k;
%let raw_dt = %scan(&monhtly_data,&i);

proc append data=&outcome_names data=excl_data_&i force;
run;

%end;
%mend;
%append;

---

[Toni2]

thank you! i have replied to my initial comment with additional information

[Reeza]

Not enough information to change the answer so far. As requested before show how you'd do it for a case without any macros and we can help generalize it. Working code. 

[PaigeMiller]

You can do most of this without macros at all. I use your one of macro variables here as it seems to work.

%let monhtly_data = month_1 month_2 month_3 month_4 month_5 month_6;
data &outcome_names;
    set &monhtly_data indsname=indsname;
    if input(compress(indsname,,'dk'),2.) in (1,2,3) then output quarter_1;
    else output quarter_2;
run;
    

[PaigeMiller]

I also think this may be the XY Problem, where you think you just have to figure out how to get the data from MONTH1 MONTH2 ... into QUARTER1 QUARTER2. But in most cases you don't have to do this. You can simply compute quarters from months, no macros needed.

So ... please explain why you need output data sets QUARTER1 QUARTER2, what will you do with them once you have them?

[Toni2]

Due to the complexity of what i do and the difficulty to simplify here for all of you to review i think it is an XY problem. i have now added in my initial post a reply with more information - please if you can have a look. thanks a lot for your advises! 🙂

[PaigeMiller]

---

@Toni2 wrote:
Due to the complexity of what i do and the difficulty to simplify here for all of you to review i think it is an XY problem. i have now added in my initial post a reply with more information - please if you can have a look. thanks a lot for your advises! 🙂

---

You didn't explain. You don't say why you need separate data sets QUARTER1 QUARTER2 ... instead of just one data set with all quarters; and you didn't explain what you are going to do with these data sets when you create them.

[ballardw]

Does your actual data have anything resembling DATE values in it? If so then this may be a next-to-unneeded request as there are many ways to subset and or format Date values. Having a variable in the data set(s) makes selection when need next to trivial. Plus FORMATS applied to dates make it easy to build report categories for date intervals.

Not to mention I have a hard time seeing this process working across year boundaries in any correct sense.

Note that there are options on the SET statement that bring in the source data set name and you can parse that text to pull stuff out at that time instead of trying to prebuild macro variable lists.

A brief example:

data month_1;
   x=3;
run;

data month_2;
   x=27;
run;

data month_3;
   x=.234;
run;
data month_4;
   x=23400;
run;

data combined;
   set month_1 - month_4
       indsname=ds;
   nummonth = input(scan(ds,2,'_'),4.);
   numqtr  = ceil(nummonth/3);
run; 

[Toni2]

thanks! actually, the months datasets include a column with date values which can be used. i am going to start working on this. I have also added a reply in my initial post with additional information. thanks a lot! 🙂

[Toni2]

hi all, thanks very much  for your answers 

i would try to answer to all your questions

I have now managed to make the code run in 3 different separate in order to progress the tasks i have to complete. As you can see the below is far from optimal

Background : the scope of the code is to number of different months apply some exclusions (step 1) then create quarterly data (step 2) and finally to be merge in another table (step 3) 

I have two problems 

1) i want to automate the code. i mean not to need to take the outcome from part one and the to add it in part 2 and then from 2 to 3

2) the below code is an example with 6 months and 2 quarters. I want to make the code general. I mean the user to add a number of months and quarters and not to be static to work only with 6 months and 2 quarters

/* set up*/
%let monhtly_data = month_1 month_2 month_3 month_4 month_5 month_6;
%let outcome_names = quarter_1 quarter_2;


/*Step-1  Exclusions*/
%macro excl;

%let k=%sysfunc(countw(&monthly_data));
%do j=1 %to &k;
%let raw_dt = %scan(&monthly_data,&j);
 
data &raw_dt._temp;
set &raw_dt
(where = (variable_1 = "OP"
and variable = "10"));
run;
%end;
%mend;

%excl;

/*step-2 Convert monthly to quarterly data */ 


data quarter_1;
   set month_1_temp month_2_temp month_3_temp;
run;

data quarter_2;
   set month_4_temp month_5_temp month_6_temp;
run;

/* step-3 Create samples based on characteristics */


proc sql;
create table quarter_1_smp  as
 select * from quarter_1_char (where = (monthid in (1,2,3))) as x left join quarter_1 as y
 on x.HOLDING_REFERENCE =y.HOLDING_REFERENCE and x.PERIOD_END_DATE =y.PERIOD_END_DATE; 
quit;


proc sql;
create table quarter_2_smp  as
 select * from quarter_2_char (where = (monthid in (4,5,6))) as x left join quarter_2 as y
 on x.HOLDING_REFERENCE =y.HOLDING_REFERENCE and x.PERIOD_END_DATE =y.PERIOD_END_DATE; 
quit;