Loop through months in variable

[Sean_OConnor]

Folks I need to create some summary monthly statistics for the same procedure each month. 

Start_date and end_date will always be the first of the given month and last of the given month.

While end_date1 will be the last day of the previous month.

Is there an easy way to set up a macro which would carry this out say for the whole year of 2017?

%let start_date='01aug2017'd; *First day of analysis month;
%let end_date='31aug2017'd; * Last day of analysis month;
%let end_date1='31jul2017'd; *Last day of lookback month month previous to above;


data testa;
set test;
if in_rppi=' ' then delete;
run;

data i;
set testa;
 if &start_date <= dt_filed <=&end_date then result="match";
 if result='match' then output i;
run;

data u;
 set test;

where dt_filed between '01JAN2010'd and &end_date;

 if substr(csoppsn,1,1) in ('X') then
 delete;

 if dc_type_participant='Vendor' and dc_type_property ='Residential' then
 output vendors;
run;

[PaigeMiller]

---

@Sean_OConnor wrote:

Folks I need to create some summary monthly statistics for the same procedure each month. 

---

PROC SUMMARY is the easiest way, not macros.

data have1;
     set have;
     month = mdy(month(dt_filed),1,year(dt_filed));
    format month monyy.;
run;
proc summary data=have1;
    class month;
    var yourvariablename;
    output out=_stats_ sum=; /* Or if you want the average, use mean= */
run;

[Reeza]

Do you have to do the calculations individually or can you use BY group processing instead?

[Sean_OConnor]

I have to do the calculations individually in the sense that the dataset is not processed already.

I've omitted some datasteps from below and have just shown the ones where we subset the datasets for given periods.

[PaigeMiller]

---

@Sean_OConnor wrote:

I have to do the calculations individually in the sense that the dataset is not processed already.

I've omitted some datasteps from below and have just shown the ones where we subset the datasets for given periods.

---

This really isn't explained. Many situations (not all) can be processed all at once.

We don't know if you are in a situation where they can be processed all at once. So let's be specific ... what "calculations" do you want to do on this data, by month?

[Reeza]

---

@Sean_OConnor wrote:

I have to do the calculations individually in the sense that the dataset is not processed already.

I've omitted some datasteps from below and have just shown the ones where we subset the datasets for given periods.

---

Then we can't really answer your question beyond saying turn it into a macro. Being able to use BY groups would be more efficient, but if you need to convert this to a macro, follow these instructions:

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

[PaigeMiller]

Some people think they are not in a situation where they can use BY processing or PROC SUMMARY to do calculations across many group, when in reality they are in such a situation. So we need to know exactly what calculations need to be done here.

[r_behata]

data _null_;

	start_date=put(intnx('month',today(),0,'B'),date9.);
	end_date=put(intnx('month',today(),0,'E'),date9.);
	end_date1=put(intnx('month',today(),-1,'E'),date9.);
	start_yr=put(intnx('year',today(),0,'B'),date9.);

	call symputx('start_date',start_date);
	call symputx('end_date',end_date);
	call symputx('end_date1',end_date1);
	call symputx('start_yr',start_yr);
run;

%put &=start_date &=end_date &=end_date1 &start_yr;


data testa;
set test;
if in_rppi=' ' then delete;
run;

data i;
set testa;
 if "&start_date"d <= dt_filed <="&end_date"d then result="match";
 if result='match' then output i;
run;

data u; 
set test;
where dt_filed between "start_yr"d and "&end_date"d; 
if substr(csoppsn,1,1) in ('X') then delete; 
if dc_type_participant='Vendor' and dc_type_property ='Residential' then output vendors;
run;

[ballardw]

data u; 
set test;
where dt_filed between "start_yr"d and "&end_date"d; 
if substr(csoppsn,1,1) in ('X') then delete; 
if dc_type_participant='Vendor' and dc_type_property ='Residential' then output vendors;
run;

The above data step fails with an error as you do not have the data set VENDORS on the DATA statement.

[Sean_OConnor]

data _null_;

	start_date=put(intnx('month',today(),0,'B'),date9.);
	end_date=put(intnx('month',today(),0,'E'),date9.);
	end_date1=put(intnx('month',today(),-1,'E'),date9.);
	start_yr=put(intnx('year',today(),0,'B'),date9.);

	call symputx('start_date',start_date);
	call symputx('end_date',end_date);
	call symputx('end_date1',end_date1);
	call symputx('start_yr',start_yr);
run;

%put &=start_date &=end_date &=end_date1 &start_yr;

Could someone provide some information as how I would  change the following code start for august 2018 and work backwards.

The above starts for the first day of the current month but I would like to start my analysis in august 2018.

[Reeza]

What do you mean by:

Could someone provide some information as how I would  change the following code start for august 2018 and work backwards.

 You can replace today() with '01AUG2018'd and it will replace today(). 

Or better yet, make it take a variable and set the variable at the top of the data step.

data _null_;
        *myDate = today();
        myDate = '01Aug2018'd;

	start_date=put(intnx('month', myDate, 0, 'B'), date9.);
	end_date=put(intnx('month', myDate, 0, 'E'), date9.);
	end_date1=put(intnx('month', myDate, -1, 'E'), date9.);
	start_yr=put(intnx('year', myDate, 0, 'B'), date9.);

	call symputx('start_date', start_date);
	call symputx('end_date', end_date);
	call symputx('end_date1', end_date1);
	call symputx('start_yr', start_yr);
run;

%put &=start_date &=end_date &=end_date1 &start_yr;

Make sure to comment out one of the dates, so only the one you need works.

---

@Sean_OConnor wrote:

data _null_;

	start_date=put(intnx('month',today(),0,'B'),date9.);
	end_date=put(intnx('month',today(),0,'E'),date9.);
	end_date1=put(intnx('month',today(),-1,'E'),date9.);
	start_yr=put(intnx('year',today(),0,'B'),date9.);

	call symputx('start_date',start_date);
	call symputx('end_date',end_date);
	call symputx('end_date1',end_date1);
	call symputx('start_yr',start_yr);
run;

%put &=start_date &=end_date &=end_date1 &start_yr;

Could someone provide some information as how I would  change the following code start for august 2018 and work backwards.

The above starts for the first day of the current month but I would like to start my analysis in august 2018.

---