topic Re: Processing multiple months in SAS Programming

Processing multiple months

luvscandy27 — Thu, 04 Mar 2021 20:50:47 GMT

Hello all, I would like to know if there is any way for me to use the code below for several months. I have several rosters from January to December. The code below processes the roster for only January therefore I have to change the code multiple times to fit each month I am working with. I'm processing them separately since I need to have a rolling twelve months i.e. for February rosters the dates would be February 28 2017 to February 2018 and so on. The data below is only sample data.

data date;
infile datalines delimiter = ',';
input ID $ DATE:mmddyy10. TYPE $11. PAIN HEART INPAT OUTPAT;
format date mmddyy10.;
datalines;
1, 05/03/2018, OUTPATIENT, ., 1, ., 1
1, 05/02/2018, OUTPATIENT, ., 1, ., 1
2, 01/26/2018, OUTPATIENT, 1, ., ., 1
2, 01/18/2018, OUTPATIENT, 1, ., ., 1
2, 01/10/2018, OUTPATIENT, 1, ., ., 1
;
run;

%macro split(cond, titlemonyr, ranstart, ranend);

/*Split the conditions*/
proc sort data=date nodupkey out=&cond._&titlemonyr.;
by id date type;
where &cond.=1 and date ge "&ranstart."d and date le "&ranend."d ;
run;

/*Determine what cases meet the case definition (2 outpatient or one inpatient unless is head trauma. Note:
head trauma is counted once regardless of inpatient or outpatient. The look back period is one year.*/
data &cond._&titlemonyr._2;
set &cond._&titlemonyr.;
retain count;
by id date type;
if first.id then count= 1;
else count = count + 1;/*adding the total appointments*/
%if &cond. ne HT %then %do;
if last.id and count = 1 and INPAT = . then delete;
%end;
run;

%mend split;
%split (PAIN, JAN2018, 31jan2017, 31jan2018);
%split (HEART, JAN2018, 31jan2017, 31jan2018);

Re: Processing multiple months

Reeza — Thu, 04 Mar 2021 21:01:03 GMT

Look into CALL EXECUTE() and how you can use it to call your macro multiple times.

Or you can redesign your entire process to handle multiple groups at once. Looking at your code, it looks like counts over overlapping periods, which makes me consider a Multilabel format and PROC TABULATE.

The first method is faster, programming wise and will definitely work with your current code.
The second method is longer, programming wise, but likely faster run time and you'll probably learn a lot.
There are probably other methods but these are the ones I'd recommend given what I see here. Which approach would you like to attempt? I can provide some more relevant references based on your answer.

Re: Processing multiple months

luvscandy27 — Thu, 04 Mar 2021 21:18:45 GMT

I'll try call execute. Any documentation would be greatly appreciated.

Re: Processing multiple months

qoit — Thu, 04 Mar 2021 21:36:46 GMT

Below should work:

data date;
	infile datalines delimiter = ',';
	input ID $ DATE:mmddyy10. TYPE $11. PAIN HEART INPAT OUTPAT;
	format date mmddyy10.;
	datalines;
1, 05/03/2018, OUTPATIENT, ., 1, ., 1
1, 05/02/2018, OUTPATIENT, ., 1, ., 1
2, 01/26/2018, OUTPATIENT, 1, ., ., 1
2, 01/18/2018, OUTPATIENT, 1, ., ., 1
2, 01/10/2018, OUTPATIENT, 1, ., ., 1
;
run;


%macro split(cond);

%macro _;
%mend _;

proc sql;
	select distinct put(date,monyy5.)
		into :date1 - :date&sysmaxlong
			from date;
	%let cnt = &sqlobs;
quit;

/*Split the conditions*/
%do i = 1 %to &cnt;
	%let d_start = %sysfunc(intnx(month,"01||&&date&i"d,0,b),date9.);
	%let d_end = %sysfunc(intnx(year,"01||&&date&i"d,0,e),date9.);
	%put &=d_start &=d_end;

	proc sort data=date nodupkey out=&cond._&&date&i.;
		by id date type;
		where &cond.=1 and date >= "&d_start"d and date <= "&d_end"D;
	run;

	/*Determine what cases meet the case definition (2 outpatient or one inpatient unless is head trauma. Note:
	head trauma is counted once regardless of inpatient or outpatient. The look back period is one year.*/
	data &cond._&&date&i.._2;
		set &cond._&&date&i.;
		retain count;
		by id date type;

		if first.id then
			count= 1;
		else count = count + 1;/*adding the total appointments*/

		%if &cond. ne HT %then
			%do;
				if last.id and count = 1 and INPAT = . then
					delete;
			%end;
	run;

%end;
%mend split;

%split (PAIN);

Re: Processing multiple months

Reeza — Thu, 04 Mar 2021 21:48:00 GMT

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

How to add data to a regular data set and report it

https://gist.github.com/statgeek/353374a5d8ea4f0c89ce5d80a47f4a4c

@luvscandy27 wrote:
I'll try call execute. Any documentation would be greatly appreciated.

Re: Processing multiple months

luvscandy27 — Thu, 04 Mar 2021 21:54:31 GMT

This is great however, I am very new at macro processing . I hate to ask this but could you provide me with an explanation of

what is happening in the code? Also, the sample data I put in the post only processes January data. With the code you provide can I process all months at one time or do I need to separate the months?

Re: Processing multiple months

qoit — Thu, 04 Mar 2021 21:59:34 GMT

Thanks to Art Carpenter from whom I learned this technique many years back. The SQL procedure moves the dates into macro variables which are then essentially used to subset the dataset based on the date conditions. It should be able to process all months; keep an eye out in the log and turn on the MPRINT and MLOGIC system options to view the code generated by the macro facility.

Re: Processing multiple months

luvscandy27 — Thu, 04 Mar 2021 22:19:45 GMT

Thank you!

Re: Processing multiple months

PaigeMiller — Thu, 04 Mar 2021 22:31:46 GMT

I'm processing them separately since I need to have a rolling twelve months i.e. for February rosters the dates would be February 28 2017 to February 2018 and so on. The data below is only sample data.

What analysis will be done once the data is split? Rolling what? Average?

If all you want is rolling averages, no macros or CALL EXECUTE are needed, you can use PROC EXPAND to get all the rolling statistics (including average, standard deviation and many many others) over all of your 12 month time periods, in one call to the PROC. Let's not decide that you have to have macros or CALL EXECUTE just yet.