Can this be a macro

[GS2]

Hello,

I am running creating reports based on the month and year. The way my code is set (below) I have to manually enter each month and year combination in for the variable "newdata", "dateyear", "rcenvar" and "data_remove". My question is it possible to create a macro of this so I could put a starting and ending month and the macro would run my macros for each combination between the time frame. Ideally, it will produce the data sets aug17, sep17, oct17 and so on like I have below but without having to manually do that for each. Thank you

*AUG17;
%report_my(olddata=forreports, newdata=aug17, dateyear=01AUG2017, rcenvar = aug17_rcen)
%report_data_update(data_remove=aug17, data_update= forreports)

*SEP17;
%report_my(olddata=forreports, newdata=sep17, dateyear=01SEP2017, rcenvar = sep17_rcen)
%report_data_update(data_remove=sep17, data_update= forreports)

*OCT17;
%report_my(olddata=forreports, newdata=oct17, dateyear=01OCT2017, rcenvar = oct17_rcen)
%report_data_update(data_remove=oct17, data_update= forreports)

*NOV17;
%report_my(olddata=forreports, newdata=NOV17, dateyear=01NOV2017, rcenvar = NOV17_rcen)
%report_data_update(data_remove=NOV17, data_update= forreports)

[ballardw]

Show what those macros do, i.e. the code.

Example data would be helpful.

For reporting purposes there is likely no reason to create separate data sets if you have proper date variable(s) in your existing data set.

[GS2]

%macro report_my(olddata=, newdata=, dateyear= , rcenvar = );
/* ADJUST THE FOLLOWING FOR WHAT REPORTS YOU WANT */
data pre_tmp;
set &olddata;
/* IF YOU WANT THE FULL, LEAVE THE FOLLOWING COMMENTED OUT */
/* Annual only - include the following:
if &rcenvar in('6 week','3 month','6 month') then delete; */
/* Annual for 1,2,3 year only (arbitrarily chosen) - include the following:
if &rcenvar ~in('1 year','2 year','3 year') then delete; */
/* All time points except for 6 week (they don't collect that anymore) - include the following: */
if &rcenvar in('6 week') then delete;
/* Minimum 2 year - include the following:
if &rcenvar in('6 week','3 month','6 month', '1 year') then delete; */
run;
/* --let macro continue-- */
proc sort data=pre_tmp; by record_id; run;
data tmp;
set pre_tmp (keep=record_id -- compliance &rcenvar);
by record_id;
if first.record_id then do;
count = .;
if &rcenvar = redcap_event_name then count = 1;
end;
if &rcenvar = redcap_event_name then count = 1;
run;
proc sort data=tmp; by record_id descending count; run;
proc sort data=tmp out=&newdata nodupkey; by record_id; run;
data &newdata;
set &newdata;
if count = . then do;
redcap_event_name = '';
compliance = 'Missing All';
end;
if deceased_date ~=''d and deceased_date < "&dateyear."d then death = 'death- prior';
if revision_date ~=''d and revision_date < "&dateyear."d then revised = 'revised- prior';
if conversion_date ~=''d and conversion_date < "&dateyear."d then conversion = 'conversion- prior';
if declined_date ~=''d and declined_date < "&dateyear."d then declined = 'declined- prior';
format compliance_final $18.;
if death = 'death- prior' then compliance_final = death;
else if revised = 'revised- prior' then compliance_final = revised;
else if conversion = 'conversion- prior' then compliance_final = conversion;
else if declined = 'declined- prior' then compliance_final = declined;
else if compliance in('Missing All','No Response') then compliance_final = 'No Response';
else if compliance = 'Complete' then compliance_final = 'Complete';
else if compliance = 'Partial' then compliance_final = 'Partial';
run;
%mend report_my;
%macro report_data_update(data_remove=, data_update=);
data toremove (drop=compliance_final); set &data_remove (keep=record_id compliance_final);
where compliance_final in('conversion- prior', 'death- prior', 'revised- prior', 'declined- prior');
run;
proc sort data=&data_update; by record_id;run;
proc sort data=toremove; by record_id;run;
data &data_update;
merge &data_update (in=a) toremove (in=b);
by record_id;
if a and not b;
run;
%mend report_data_update;

Then I run the previous code for each individual data set (what I posted originally)

Then each of those month and year combination data sets go into the following macro

%macro report_freqs(datain=,date9var=);
title "&datain.";
data &datain; set &datain;
if compliance_final in('Complete','Partial') then compliance_final2 = 'Complete & Partial';
else if compliance_final = 'No Response' then compliance_final2 = 'No Response';
array compvars tot_comp_faam tot_comp_ffi_34 tot_comp_vr12 tot_comp_aofas_pt tot_comp_aofas_md tot_comp_vas tot_comp_survey lvisitxrdate;
array newcompvars[8] $ tot_comp_faam2 tot_comp_ffi_342 tot_comp_vr122 tot_comp_aofas_pt2 tot_comp_aofas_md2 tot_comp_vas2 tot_comp_survey2 lvisitxrdate2;
do i=1 to dim(compvars);
if compvars(i) in('Comp OBERD','Complete', 'Not Missing') then newcompvars(i) = 'Complete';
else if compvars(i) in('Missing All','Partial', 'Missing') then newcompvars(i) = 'Other';
else newcompvars(i) = 'ERROR';
end;
run;
proc freq data=&datain;
ods output onewayfreqs=a;%*GS added;
where dos <= "&date9var."d ;
table compliance_final / nocum nopercent;
run;
proc freq data=&datain;
ods output onewayfreqs=b;%*GS added;
where dos <= "&date9var."d and compliance_final in('Complete','No Response','Partial');
table compliance_final2;
run;
proc freq data=&datain;
ods output onewayfreqs=c;%*GS added;
where dos <= "&date9var."d and compliance_final in('Complete','No Response','Partial');
table new_clinicdate;
run;
proc freq data=&datain;
ods output CROSSTABFREQS=d;%*GS added;
where dos <= "&date9var."d and compliance_final = 'Partial';
table (lvisitxrdate2 tot_comp_faam2 tot_comp_ffi_342 tot_comp_vr122 tot_comp_aofas_pt2 tot_comp_aofas_md2 tot_comp_vas2 tot_comp_survey2)*compliance_final /missing norow nopercent;
run;
title;
%mend report_freqs;

%report_freqs(datain=aug17, date9var=01AUG2017)

[PaigeMiller]

Agreeing with @ballardw — without strong justification to use macros here, you would be better off keeping all data sets together (i.e. as one large data set with many months) and using a BY statement to produce the analysis and reports, and then macros are not needed.

I'd be happy to help with the non-macro solution. Let's discuss further.

[GS2]

My idea is to have all of the reports automated so that quarterly I can import the new data and run without having to manually add dates/change by statements to get the number I need. I select pieces of information from the outputs of the %report_freqs that I need so I thought a macro would be the best way to put all of the data in and return the data I am looking for but I want to take it a step further and have a macro perform this for each month and year combination so I do not have to.

[PaigeMiller]

If I am understanding you properly:

This can all be done with a BY statement in your analysis and reporting. Which is an awful lot simpler to program than a macro to do this. SAS has already done the hard work to make its code dynamic, so you don't have to.

[ballardw]

Periods that appear to be selected with this

* IF YOU WANT THE FULL, LEAVE THE FOLLOWING COMMENTED OUT */
/* Annual only - include the following:
if &rcenvar in('6 week','3 month','6 month') then delete; */
/* Annual for 1,2,3 year only (arbitrarily chosen) - include the following:
if &rcenvar ~in('1 year','2 year','3 year') then delete; */
/* All time points except for 6 week (they don't collect that anymore) - include the following: */
if &rcenvar in('6 week') then delete;
/* Minimum 2 year - include the following:
if &rcenvar in('6 week','3 month','6 month', '1 year') then delete; */
run;

might be better accomplished with 2 parameters, number of intervals and the interval (or parse this not nice one).

The Function INTNX can calculate a new date using those values such as Intnx("year",somedatevar,3,'E') would return 31 Dec (the 'E' is end) 3 years after the value of the Somedatevar (assuming it is a valid date value).

If you need to select Prior then the interval would be negative, -3, instead of 3.

So if you pass a macro variable like Interval with value of week, month or year that becomes the first parameter in the INTNX function and then the appropriate number of intervals. You can get different alignments of 'B', begin if the interval, 'S', same day (as close as practical).

So you could create a filter like:

Where intnx("&interval.",&somedate., &numintervals.,"B") le datevariable le &somedate. ;

to select values of your datavariable in the data set and a range of values from a parameter date. (the above assumes you a calculating an earlier variable than Somedate, change direction by using GE (or GT) depending on want.

Given any desired data value you can get a lot things you might need with either the INTNX function, the INTCK function (to count intervals between dates (times or datetimes), the various functions such as Year, Month, Day to extract bits and formatting dates to create groups

[Reeza · Accepted solution]

There's also CALL EXECUTE to call your macro in a data driven methodology.

[Shmuel]

You can do your loop with next code, just edit the first two lines:

%let from_mm = AUG2017;
%let upto_mm = NOV2017;
data _null_;
  from_dt = input("01&from_mm",date9.);
  upto_dt = input("01&upto_mm",date9.);
  put from_dt= date9. upto_dt= date9.;
  
  do until(from_dt > upto_dt);
     dtx = upcase(put(from_dt,date9.));
     mmy2 = lowcase(substr(dtx,3,5));
     call symput('DTX',strip(dtx));
     call symput('MMy2', strip(mmy2));
     
     call execute("%report_my(olddata=forreports, newdata=&mmy2, dateyear=&dtx, rcenvar = &mmy2._rcen);" ||
                   "%report_data_update(data_remove=&mmy2, data_update= forreports);" ||
                   "run;");
                   
     from_dt = intnx('month',from_dt,1,'b');
  end;
run;