Solved: trying to create SAS datasets for every month falling under a quarter ...

Santt0sh · Posted 09-23-2022 02:25 PM

Hi All,
I am trying to create SAS datasets for every month falling under a quarter using a do loop, I am sure its something to do with my do loop which is creating multiple datasets for the same month.
please suggest this.

DATA _NULL_;
	YR_START_DATE=INTNX('MONTH', DATE(), -1, 'B');
	YR_END_DATE=INTNX('MONTH', YR_START_DATE, -12);

	DO UNTIL(YR_END_DATE>YR_START_DATE);
		i+1;
		MNTH_START_DATE=YR_START_DATE;
		MNTH_END_DATE=INTNX('MONTH', MNTH_START_DATE, 0, 'e');
		PREVDTE=INTNX('MONTH', MNTH_START_DATE, -1, 'S');

		/*PREVIOUS MONTHS DATE*/
		PUT MNTH_START_DATE=YYMMDD10. MNTH_END_DATE=YYMMDD10.  PREVDTE=YYMMDD10.;
		CALL SYMPUT(CATS('MNTH', I), PUT(MNTH_START_DATE, YYMMN6.));
		CALL SYMPUT(CATS('MNTHSTDT', I), PUT(MNTH_START_DATE, YYMMN6.)); /*start date current month i.e. reporting period month t-1 month*/
		CALL SYMPUT(CATS('MNTHENDDT', I), PUT(MNTH_END_DATE, YYMMN6.));  /*end date current month i.e. reporting period month t-1 month*/
		

		/*FIRST MONTHS DATE YYMM AS TRANSACTION DATE*/
		CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); /*atm transaction for the reporting month month*/

		/*PREVIOUS MONTH DATE YYMM AS TRANSACTION DATE*/
		CALL SYMPUT(CATS('PREVDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -1, 'S'),YYMMDD10.)); /*previous month date*/
		CALL SYMPUT(CATS('BGNDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -3, 'S'),YYMMDD10.));  /*match to fetch the qtrly start date from the source format*/
		CALL SYMPUT(CATS('ENDDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -2, 'E'),YYMMDD10.));	  /*match to fetch the end qtrly date from the source format*/
		CALL SYMPUT(CATS('DATE', I), PUT(MNTH_START_DATE, YYMMN6.)); /*transaction for previous months*/
	  	CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.)); /*atm transaction for previous months*/
		

		/*PREVIOUS MONTH DATE YYMMDD AS TRANSACTION DATE*/
		YR_START_DATE=INTNX('MONTH', YR_START_DATE, -1);
	END;
	PUT YR_START_DATE YR_END_DATE;
RUN;

/* setting up date macro variables.*/

%MACRO TEST();
%GLOBAL i j STARTDT MNTHSTRDT MNTHENDT PRDDTE PRDENDDTE MNTHSTDT MNTHENDDT DATE ATMDATE ENDDT;
%DO j = 1 %TO 13;
%DO i = 3 %TO 13 %BY 3;
%PUT ITERATION&i. ====>> &i.;
	%LET STARTDT&i. = %SYSFUNC(INTNX(MONTH,%SYSFUNC(TODAY()),-&i.),6.); /*match to fetch the data from the source format*/
 	%PUT STARTDT&i.===>> &&STARTDT&i.; 
	%LET ENDDT&i. = %SYSFUNC(INTNX(MONTH,&&STARTDT&i.,3),6.); /*match to fetch the data from the source format*/
	%PUT ENDDT&i.===>> &&ENDDT&i.;
	%LET MNTHSTRDT&i. = %SYSFUNC(INTNX(MONTH,%SYSFUNC(TODAY()),-&i.,B),YYMMDD10.); /*start date of the quarter */
 	%PUT MNTHSTRDT&i. ====> &&MNTHSTRDT&i.; 
	%LET MNTHENDT&i. = %SYSFUNC(INTNX(MONTH,&&STARTDT&i.,-3,E),YYMMDD10.);  /*END date of the quarter */
	%PUT MNTHENDT&i. ====> &&MNTHENDT&i.; 
	%PUT JLOOP ===>>  &&STARTDT&i.;
	%END;
%END;
%MEND;
%TEST;







 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         %MACRO TEST();
 70         %GLOBAL i j STARTDT MNTHSTRDT MNTHENDT PRDDTE PRDENDDTE MNTHSTDT MNTHENDDT DATE ATMDATE ENDDT;
 71         %DO j = 1 %TO 13;
 72         %DO i = 3 %TO 13 %BY 3;
 73         %PUT ITERATION&i. ====>> &i.;
 74         %LET STARTDT&i. = %SYSFUNC(INTNX(MONTH,%SYSFUNC(TODAY()),-&i.),6.);
 75          %PUT STARTDT&i.===>> &&STARTDT&i.;
 76         %LET ENDDT&i. = %SYSFUNC(INTNX(MONTH,&&STARTDT&i.,3),6.);
 77         %PUT ENDDT&i.===>> &&ENDDT&i.;
 78         %LET MNTHSTRDT&i. = %SYSFUNC(INTNX(MONTH,%SYSFUNC(TODAY()),-&i.,B),YYMMDD10.);
 79          %PUT MNTHSTRDT&i. ====> &&MNTHSTRDT&i.;
 80         %LET MNTHENDT&i. = %SYSFUNC(INTNX(MONTH,&&STARTDT&i.,-3,E),YYMMDD10.);
 81         %PUT MNTHENDT&i. ====> &&MNTHENDT&i.;
 82         %PUT JLOOP ===>>  &&STARTDT&i.;
 83         %END;
 84         %END;
 85         %MEND;
 86         %TEST;
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 ITERATION3 ====>> 3
 STARTDT3===>> 22797
 ENDDT3===>> 22889
 MNTHSTRDT3 ====> 2022-06-01
 MNTHENDT3 ====> 2022-03-31
 JLOOP ===>>  22797
 ITERATION6 ====>> 6
 STARTDT6===>> 22705
 ENDDT6===>> 22797
 MNTHSTRDT6 ====> 2022-03-01
 MNTHENDT6 ====> 2021-12-31
 JLOOP ===>>  22705
 ITERATION9 ====>> 9
 STARTDT9===>> 22615
 ENDDT9===>> 22705
 MNTHSTRDT9 ====> 2021-12-01
 MNTHENDT9 ====> 2021-09-30
 JLOOP ===>>  22615
 ITERATION12 ====>> 12
 STARTDT12===>> 22524
 ENDDT12===>> 22615
 MNTHSTRDT12 ====> 2021-09-01
 MNTHENDT12 ====> 2021-06-30
 JLOOP ===>>  22524
 87         
 88         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 98

PaigeMiller · Posted 09-23-2022 02:32 PM

Usually, the idea of breaking up a large data set into smaller data sets is unnecessary, and I can see you have spent a lot of time on this already without success. Either use BY processing, or use WHERE statements to extract the part of the larger data set when you need them.

Example:

proc something data=Y2022Q1;
    where '01JAN2022'd <= data <= '31JAN2022'd;
    ... more statements go here ...
run;

--
Paige Miller

View solution in original post

PeterClemmensen · Posted 09-23-2022 02:28 PM

I don't understand this: "my do loop its creating datasets for every iteration". I don't see any data sets being created?

The DATA to DATA Step Macro
Blog: SASnrd

Santt0sh · Posted 09-23-2022 02:41 PM

Hi peter,

Thnak you for your response.

I have not created datasets in the code posted. i tried creating datasets earlier but it was creating more than what was expected.

PaigeMiller · Posted 09-23-2022 02:32 PM

Usually, the idea of breaking up a large data set into smaller data sets is unnecessary, and I can see you have spent a lot of time on this already without success. Either use BY processing, or use WHERE statements to extract the part of the larger data set when you need them.

Example:

proc something data=Y2022Q1;
    where '01JAN2022'd <= data <= '31JAN2022'd;
    ... more statements go here ...
run;

--
Paige Miller

Santt0sh · Posted 09-23-2022 02:43 PM

Hi,

Thank you for your response.

I agree I have spent a lot of time on this. but I need to create the qtrly datasets for a few tables and try to run it in a macro by creating date parameters.

Kindly suggest.

Tom · Posted 09-24-2022 12:43 AM

Show what SAS code you are trying to create to get help with making a macro that will create that code.

First question is what does this mean

datasets for every month falling under a quarter

A quarter consists of three months.

Do you want to make one dataset that has the data for all three months in that quarter?

Or do you want to make three datasets, one for each month in the quarter?

trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

Re: trying to create SAS datasets for every month falling under a quarter using a do loop,

The 2025 SAS Hackathon Kicks Off on June 11!

SAS Training: Just a Click Away