Create a monthly dataset

[NewUsrStat]

Hi guys, 

I have to create a dataset containing months from 01JAN2014 to 31DEC2022 like this: 

01JAN2014

01FEB2014

01MAR2014

.......................

01JAN2022

.......................

01DEC2022

 Can anyone help me please? 

Thank you in advance

[antonbcristina]

Hi @NewUsrStat, it isn't immediately apparent what your end goal is here. Are you hoping to create separate datasets for each month (eg. a JAN dataset, a FEB dataset...). If you could please provide a sample input dataset and what you would like the output datasets to contain, along with code you've tried. 

[NewUsrStat]

Hi thank you for your feedback! I need to create a single dataset with one column as I showed. Nothing more. I have no input data. I only have to create the dataset ex novo.

[sbxkoenk]

data work.abc;
 do i=0 to 100;
  maand = INTNX('MONTH','01JAN2014'd,i,'SAMEDAY');
  output;
 end;
 format maand MONYY5.;
run;

Koen

[antonbcristina · Accepted solution]

There's a few ways to achieve this. You could use DO loops to go through all of the years and months, and the MDY function to construct the date using the first of each month like so:

data want;
	do y=2014 to 2022;
		do m=1 to 12;
			date=mdy(m,01,y);
			output;
		end;
	end;
	format date date9.;
run;

Alternatively, you could use the INTNX function to increment by one month from a chosen start date, and use a DO UNTIL loop to ensure you've reached your desired end date.

data want2;
	date="01jan2014"d; 
	output;
	do until (date>="01dec2022"d);
		date = intnx("MONTH",date,1,"BEGINNING");
		output;
	end;
	format date date9.;
run;