topic Re: Loop through months in variable in SAS Enterprise Guide

Loop through months in variable

Sean_OConnor — Wed, 19 Dec 2018 16:11:45 GMT

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;

Re: Loop through months in variable

PaigeMiller — Wed, 19 Dec 2018 16:18:38 GMT

@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;

Re: Loop through months in variable

Reeza — Wed, 19 Dec 2018 16:18:21 GMT

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

Re: Loop through months in variable

Sean_OConnor — Wed, 19 Dec 2018 16:20:46 GMT

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.

Re: Loop through months in variable

PaigeMiller — Wed, 19 Dec 2018 16:27:01 GMT

@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?

Re: Loop through months in variable

r_behata — Wed, 19 Dec 2018 16:28:33 GMT


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;

Re: Loop through months in variable

Reeza — Wed, 19 Dec 2018 16:36:38 GMT

@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

Re: Loop through months in variable

PaigeMiller — Wed, 19 Dec 2018 17:51:32 GMT

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.

Re: Loop through months in variable

ballardw — Wed, 19 Dec 2018 18:22:37 GMT

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.

Re: Loop through months in variable

Sean_OConnor — Fri, 18 Jan 2019 12:09:23 GMT

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.

Re: Loop through months in variable

Reeza — Fri, 18 Jan 2019 16:04:10 GMT

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.