Generating Reiterated Months and Years with Do Loop

[dbcrow]

Hi, SAS Community-

This shouldn't be difficult, but I'm having a rough go of it.  I'm trying to generate a file in which each of the 58 California counties has 108 data points that are monthly increments (12) within years for nine (9) years of data, 2010-2018 (9 x 12 = 108).  The way I'm setting it up, the file has four columns:  county, date, year, and month.  So, for the first county, Alameda, we would have:  

County     Date                 Year     Month

Alameda  01/01/2010       2010    1

Alameda  02/01/2010       2010    2

.

.

.

Alameda  12/01/2018       2018    12

The problem with the code that follows is that when I get to the end of the first county, Alameda, instead of the date counter starting over at 01/01/2010 in the next county (Alpine) the date continues.  

What I want is this:  

Alpine       01/01/2010     2010       1

What I get is this:  

Alpine       01/01/2019     2019        1

Here's the code:  

data template(keep=county yearmonth month year);
	retain county yearmonth month year;
	yearmonth = "01dec2009"d;
	do i = 1 to n;
		do year = 2010 to 2018;
			do month = 1 to 12;
				yearmonth = intnx("month",yearmonth,1); 
				set counties nobs=n point=i;
			output;
			end;
		end;
	end;
stop;
format yearmonth MMDDYYS.;
run;

Note that the data set "counties" is simply a list of the 58 counties.  

Any pointers would be greatly appreciated.  

Regards,

David

[Tom · Accepted solution]

Put the DO loop(s) after the SET statement.  So you read in one county and output multiple observations for that county.

%let start='01JAN2010'd;
%let end='01DEC2018'd;
data template(keep=county yearmonth month year);
  set counties (keep=county);
  do i=0 to intck('month',&start,&end);
     yearmonth=intnx('month',&start,i);
     year=year(yearmonth);
     month=month(yearmonth);
    output;
  end;
  format yearmonth MMDDYYS10.;
run;

[Astounding]

An easy formula to replace the current one:

yearmonth = mdy(month, 1, year);

It would be faster to execute the SET statement sequentially:

data template(keep=county yearmonth month year);
   set counties;
		do year = 2010 to 2018;
			do month = 1 to 12;
				yearmonth = mdy(month, 1, year);
			output;
			end;
		end;
	end;
stop;
format yearmonth MMDDYYS.;
run;

The order of the observations would change, however.  It's not clear which order would be best, but you can always sort the data.

[dbcrow]

Many thanks! Worked like a charm.

[dbcrow]

This is also a great solution.  I got it a few seconds too late to accept it as a solution, unfortunately.  (The SAS Users Community really should allow someone to accept more than one solution.)  Many thanks as well.  

[ballardw]

---

@dbcrow wrote:

This is also a great solution.  I got it a few seconds too late to accept it as a solution, unfortunately.  (The SAS Users Community really should allow someone to accept more than one solution.)  Many thanks as well.  

---

Don't worry about marking only a "first" solution as correct. The main thing is that a solution is indicated so that others searching for forum for similar issues can tell that particular question was solved somehow.

Depending on the specific problem there are often 3 or, some times many, more "correct" solutions.